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)
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"
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
I am hoping i have explained in enough detail and that it makes sense. If anyone could help me it would be great