Looping through a table range

[Legacy 93538]

<HR style="BACKGROUND-COLOR: #ebebeb; COLOR: #ebebeb" SIZE=1> <!-- / icon and title --><!-- message --><!-- BEGIN TEMPLATE: ad_showthread_firstpost_start --><!-- END TEMPLATE: ad_showthread_firstpost_start -->Hi

This probably an odd problem but i am stuck and i have no idea where to start in solving it so i am hopign someone can help.

I have a workbook which has two tables on it one table just has values and the other table has forumla in it. the two tables are identically except for the contents of the cell.

What i need to do is create a macro which opens this workbook and also create a new workbook and then loop through the table with the forumla and look at the forumla and then edit the forumla so it uses the variables in the other table.It should then take that value and paste into the new workbook in column 4 of the sheet added and for each cell in teh range move to the next cell in column 4 of the new workbook.

For example if the forumla is A1 / A2 then i would need it to look at the correpsonding table and look at correpsonding cells and grap the values edit the fourmla so it comes out like b10000 / b20000

So far i have written the macro to create the first bit which opens the workbook, creates the new workbook and loops through the cell range and looks at whether its got text but then i cant think how to do the next bit.

I should explain the variables. PPCWBSht is the sheet within the workbook, PPFWB is the workbook with the tables.

'Version number of Input Reference Form to be convert
ConvertVN = Application.InputBox("Please enter the version number of the Input Reference Form to be convert", "Convert Version Entry Box", SConvertVN)


'Is previously manually entered data to be imported
ManualVN = MsgBox("Would you like any data entered manually in previous versions to be imported", vbQuestion + vbYesNo, "Manual Version question box")
    If ManualVN = vbYes Then
        ' If yes to ManualVN then what version number to import from
        ManualImportVN = Application.InputBox("Please enter the version number of Calculations Table the data is to be imported from", "Manual Version Entry Box", SManualVN)
    End If

'Set variables
strFldr = "R:\HondaCarsEurope\Markets\Germany\PPIII\Tables"
Set PPCWB = Application.Workbooks.Add
PPCWB.Sheets.Add.Name = "CalcAPD"
Set PPCWBSht = PPCWB.Sheets("CalcAPD")
Set PPFWB = Application.Workbooks.Open(strFldr & "/" & "HDE_PPIII_MONTH_Input_Reference_form_V" & ConvertVN & ".xlsx")


'Add column titles to new workbook
Application.StatusBar = "Open New workbook"
PPCWBSht.Activate
ColN = 1
PPCWBSht.Cells(ColN, 1).Resize(, 8).Value = Array("CalcID", "CalcDescription", "Calcname", "Calculations", _
"Department", "Category", "NumFormat", "ChartOrder")


'Loop through the form cells and populate the calculations table
Application.StatusBar = "Loop through calcs Form and populate calculations table"
For Each cell In PPFWB.Sheets("IRFORM").Range("F4:U1000")
    If cell.Value <> "" Then
              
    End If

Next cell

PPFWB.Close

I cant attach a copy of the workbook with the tables, so i cant give an example sorry

I am hoping i have explained in enough detail and that it makes sense. If anyone could help me it would be great

Thanks

Jessicaseymour

 

[sijpie]

You can always store the spreadsheet on a sharing site and post the link here.

For example if the forumla is A1 / A2 then i would need it to look at the correpsonding table and look at correpsonding cells and grap the values edit the fourmla so it comes out like b10000 / b20000

Don't understand where th B comes from and where the 1000 comes from.

Say the first cell of the 'Formula' table has A1/A2
and the first cell of the value table contains 200
then what needs to be done?