Macro to set a vlookup

[northern225]

Hello,

I am looking for a macro to insert a vlookup in row 2 for every 5th column? Current vlookup forumla: vlookup(E1,vlookup!$A:$B,2,0)

Any help would be appreciated!

 

[Joe4]

How far out should it go (which column does it stop at)?

 

[Joe4]

Let's say that you wanted it to go out to column 100 (so you would have 20 VLOOKUP formulas). Then the macro might look something like this:

Sub VlookupMacro()

    Dim myCol As Long
    
    For myCol = 5 To 100 Step 5
        Cells(2, myCol).FormulaR1C1 = "=VLOOKUP(R[-1]C,vlookup!C1:C2,2,0)"
    Next myCol
    
End Sub

 

[northern225]

The most recent data set ended at column ALQ but the ending column varies in increments of 5.

 

[Joe4]

It looks like column ALQ is column number 1005. So just replace the loop row in my previous code to:

For myCol = 5 To 1005 Step 5

 

[northern225]

Works very nicely, all I have to do is change the 100 to the number of columns in my data set.

Thanks very much