Multiple to insert multiple vlookup formulas

[Rid1]

Good day all,

I am looking for a macro to allow me to insert multiple vlookups onto a spreadsheet which is utilised as a template. My template basically creates another sheet and transfers the data as well, thereafter clears the selected range from the template thereby also removing the formulas as well. I would like a macro that would reinsert these formulas. I tried to record a macro by physically inserting the formulas one by one but I get a run time error. The recorded macro is below

Option Explicit
Sub Insert_Formulas()
'
' Insert_Formulas Macro
'

'
    ActiveCell.Offset(-12, -24).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(1, -1).Range("A1:C1").Select
    Selection.ClearContents
    ActiveCell.Offset(-35, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'C:\Users\Ridwan\Dropbox\Petrocam Finance (Group)\Repository\[PFI_Archive.xlsm]Summary'!R2C2:R1000C17,3),"""")"
    ActiveCell.Offset(-34, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'C:\Users\Ridwan\Dropbox\Petrocam Finance (Group)\Repository\[PFI_Archive.xlsm]Summary'!R2C2:R1000C17,4),"""")"
    ActiveCell.Offset(-33, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'C:\Users\Ridwan\Dropbox\Operations (Group)\petrocam operations (Group)\Work in Progress\[Operation Workbook Summary.xlsx]Operation Workbook Summary'!R2C2:R1000C12,7,0),0)"
    ActiveCell.Offset(-32, 26).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "IFERROR(VLOOKUP($B$2,'C:\Users\Ridwan\Dropbox\Operations (Group)\petrocam operations (Group)\Work in Progress\Repository\[Operation Workbook Summary.xlsx]Operation Workbook Summary'!$B$2:$L$1000,4,0),0)"
    ActiveCell.Offset(-32, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,4,0),0)"
    ActiveCell.Offset(-31, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,5,0),0)"
    ActiveCell.Offset(-30, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,6,0),0)"
    ActiveCell.Offset(-29, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,8,0),0)"
    ActiveCell.Offset(-27, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,9,0),0)"
    ActiveCell.Offset(-26, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,10,0),0)"
    ActiveCell.Offset(-25, 1).Range("A1:C1").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(R2C2,'Operation Workbook Summary.xlsx'!R2C2:R1000C12,11,0),0)"
    ActiveCell.Offset(-23, 1).Range("A1:C1").Select
    ActiveCell.Offset(21, 2).Range("A1").Select
    Selection.ClearContents
    ActiveCell.Offset(-1, -2).Range("A1").Select
    Selection.ClearContents
        ActiveWorkbook.Save
End Sub 

Your help will be most appreciated.

[Richard Buttrey]

Hi,

The workbook would be more useful, particularly since we don't know what the active cell was at the time you recorded the macro.

In any case, ignore the macro you have. Recorded macros are occosionally a useful starting point, but they always need editing and modifying to make them truly workable.

Tell us exactly what you are trying to do in a narrative form. It appears that you are wanting to enter one or more VLOOKUP formula in several cells. Tell us what the formulae are attempting to do and where you expect them to be placed. And if this is not always the same place, what rules should be used for placing the formulae

[Rid1]

Hi

Please find attached,basically it the sheet has look ups to another summary sheet (Pro Forma Invoice Summary) from which it extracts the information and compares this to the LC data inputted into Column C. Prior I had two macros, one to move to copy data to another sheet and another to clear cells. Formulas are also included in the cells as comments and want these to be automatically inserted.

I have changed the macro since, thou I am having an error on the spreadsheet and made a note in the document. The idea now is that the sheet is duplicated then moved to the archive workbook behind the summary sheet and stored there, a PDF is created, named and stored in separate location and the template files is then closed.

Your advise on the volookups message will be most appreciated. Placing of formulas will not change.