Duplicating VLOOKUP macro in multiple sheets

Question

I recorded a macro that VLOOKUPs from Sheet "P&L" (the first tab that holds all of the data) and filters down in the current sheet until the data in column A runs out. It works; however, I need this code to function for the remaining sheets. These are updated monthly. There will be a different number of inputs in Column A in each sheet. These are all ID #s I'm using to vlookup information from the P&L tab.

When I wrote this macro as a FoorLoopIndex, I keep getting "Compile error: invalid or unqualified" messages.

I do not have any experiences with macros -- I'm struggling to find my error.

Sub update_gp_profits()

Dim StartIndex, EndIndex, LoopIndex As Integer
StartIndex = Sheets("P&L").Index + 1
EndIndex = Sheets("Sheet4").Index - 1

For LoopIndex = StartIndex To EndIndex

lastrow = .Range("A" & .Rows.Count).End(xlUp).Row

.Range("B2:B" & lastrow).Formula = "=+VLOOKUP(RC[-1],'P&L'!R15C3:R29702C4,2,FALSE)"
    Range("C2").Select
.Range("C2:C" & lastrow).Formula = "=+VLOOKUP(RC[-2],'P&L'!R15C3:R29702C5,3,FALSE)"
    Range("D2").Select
.Range("D2:D" & lastrow).Formula = "=+VLOOKUP(RC[-3],'P&L'!R15C3:R29702C6,4,FALSE)"
    Range("E2").Select
.Range("E2:E" & lastrow).Formula = "=+VLOOKUP(RC[-4],'P&L'!R15C3:R29702C17,15,FALSE)"
    Range("F2").Select
.Range("F2:F" & lastrow).Formula = "=+VLOOKUP(RC[-5],'P&L'!R15C3:R29702C18,16,FALSE)"
    Range("J2").Select
.Range("k2:k" & lastrow).Formula = "=+VLOOKUP(RC[-10],'P&L'!R15C3:R29702C160,158,FALSE)"
Range("k2").Select

Next LoopIndex
End Sub

In your code listed above i do not see any sheet1 and sheet2. i saw `Sheets("P&L")` and `Sheets("Sheet4")` please modifed the question. thx — Hiten004, Apr 14 '15 at 20:20
123 did not have VBA. If it is possible to learn VBA, it should be possible to learn formula syntax in Excel, too. Just sayin'. — teylyn, Apr 14 '15 at 21:15

score 0 · Answer 1 · answered Apr 14 '15 at 20:55

Try this one,

Sub update_gp_profits()
Dim ws As Worksheet
Dim rng As Range
Dim lRow As Long

Set ws = ActiveSheet
      '
    With ws
            lRow = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row ' Last row

            Set rng = .Range("A2" & ":" & "A" & lRow) ' This is your range
                rng.Offset(0, 1).FormulaR1C1 = "=VLOOKUP(RC[-1],'P&L'!R15C3:R29702C4,2,FALSE)"
                rng.Offset(0, 2).FormulaR1C1 = "=VLOOKUP(RC[-2],'P&L'!R15C3:R29702C5,3,FALSE)"
                rng.Offset(0, 3).FormulaR1C1 = "=VLOOKUP(RC[-3],'P&L'!R15C3:R29702C6,4,FALSE)"
                rng.Offset(0, 4).FormulaR1C1 = "=VLOOKUP(RC[-4],'P&L'!R15C3:R29702C17,15,FALSE)"
                rng.Offset(0, 5).FormulaR1C1 = "=VLOOKUP(RC[-5],'P&L'!R15C3:R29702C18,16,FALSE)"
                rng.Offset(0, 10).FormulaR1C1 = "=VLOOKUP(RC[-10],'P&L'!R15C3:R29702C160,158,FALSE)"

            Debug.Print rng.Address
    End With

End Sub

awesome - it moves through the active worksheet rather than updating each sheet. thanks for your help! @chito — ja2255, Apr 20 '15 at 14:52

score 0 · Answer 2 · edited May 23 '17 at 12:22

Try below code it will loop all the rows on the sheet4. max num of row in 2010 office = https://stackoverflow.com/a/527026/1411000 https://stackoverflow.com/a/527026/1411000

Sub update_gp_profits()

Dim StartIndex, EndIndex, LoopIndex As Integer
StartIndex = Sheets("Sheet4").).Index + 1
EndIndex =  1048576    
For LoopIndex = StartIndex To EndIndex

lastrow = .Range("A" & .Rows.Count).End(xlUp).Row

.Range("B2:B" & lastrow).Formula = "=+VLOOKUP(RC[-1],'P&L'!R15C3:R29702C4,2,FALSE)"
    Range("C2").Select
.Range("C2:C" & lastrow).Formula = "=+VLOOKUP(RC[-2],'P&L'!R15C3:R29702C5,3,FALSE)"
    Range("D2").Select
.Range("D2:D" & lastrow).Formula = "=+VLOOKUP(RC[-3],'P&L'!R15C3:R29702C6,4,FALSE)"
    Range("E2").Select
.Range("E2:E" & lastrow).Formula = "=+VLOOKUP(RC[-4],'P&L'!R15C3:R29702C17,15,FALSE)"
    Range("F2").Select
.Range("F2:F" & lastrow).Formula = "=+VLOOKUP(RC[-5],'P&L'!R15C3:R29702C18,16,FALSE)"
    Range("J2").Select
.Range("k2:k" & lastrow).Formula = "=+VLOOKUP(RC[-10],'P&L'!R15C3:R29702C160,158,FALSE)"
Range("k2").Select

Next LoopIndex
End Sub

Duplicating VLOOKUP macro in multiple sheets

2 Answers2