excel - Duplicating VLOOKUP macro in multiple sheets -
i recorded macro vlookups sheet "p&l" (the first tab holds of data) , filters down in current sheet until data in column runs out. works; however, need code function remaining sheets. these updated monthly. there different number of inputs in column in each sheet. these id #s i'm using vlookup information p&l tab.
when wrote macro foorloopindex, keep getting "compile error: invalid or unqualified" messages.
i not have experiences macros -- i'm struggling find error.
sub update_gp_profits() dim startindex, endindex, loopindex integer startindex = sheets("p&l").index + 1 endindex = sheets("sheet4").index - 1 loopindex = startindex 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
try one,
sub update_gp_profits() dim ws worksheet dim rng range dim lrow long set ws = activesheet ' ws lrow = ws.cells.find("*", searchorder:=xlbyrows, searchdirection:=xlprevious).row ' last row set rng = .range("a2" & ":" & "a" & lrow) ' 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 end sub
Comments
Post a Comment