i'm using vlookup pull date sheet , i'm referencing cell in sheet want pull to. want drag vlookup down can't figure out how anchor formula remains same when drag down. i'm referencing cell "i2" , want vlookup reference "i3" , on, i'm not sure how code it. appreciated! here's code:
dim lastrow long dim storedata range lastrow = activesheet.cells(rows.count, "c").end(xlup).row set storedata = sheets("list of stores").range("a2:c" & lastrow) sheets("sj360 source 140").select range("h1").select selection.entirecolumn.insert , copyorigin:=xlformatfromleftorabove on error goto myerrorhandler: dim x thisworkbook.worksheets("sj360 source 140") x = application.worksheetfunction.vlookup(range("i2"), storedata, 3, false) range("h2").value = x end dim fillformula variant x = x + 1 thisworkbook.sheets("sj360 source 140") range("h2").select activecell.offset(x, 0).select fillformula = "vlookup(x), storedata, 3, false)" .range("h2:h&lastrow").formula = fillformula .range("h&lastrow").filldown end myerrorhandler: if err.number = 1004 msgbox "value not found" end if i tried make x "i2" "i3" etc didn't right.
try this. when trying fill same formula down range use r1c1:
dim lastrow long dim storedata range lastrow = activesheet.cells(rows.count, "c").end(xlup).row set storedata = sheets("list of stores").range("a2:c" & lastrow) sheets("sj360 source 140") .range("h1").entirecolumn.insert , copyorigin:=xlformatfromleftorabove .range("h2:h" & lastrow).formular1c1 = "vlookup(rc1," & storedata.address(1, 1, xlr1c1, true) & ",2,false)" end if want value in cells use this:
dim lastrow long dim storedata range lastrow = activesheet.cells(rows.count, "c").end(xlup).row set storedata = sheets("list of stores").range("a2:c" & lastrow) sheets("sj360 source 140") .range("h1").entirecolumn.insert , copyorigin:=xlformatfromleftorabove .range("h2:h" & lastrow).value = .evaluate("index(vlookup(i2:i" & lastrow & "," & storedata.address(1, 1,,true) & ",2,false),)") end 