Thread: Macros- v look up populating to the last row PLEASE HELP

Ok, I am by no means an excell whizz, but I do have some experience. I have a long macros calculating costs and when to dispatch work orders. I run this each week. The number of rows changes from week to week. I am looking to put a v look up in the macros to automatically stop at the last populated row.

I have:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Sheet2!C[-27]:C[-26],2,FALSE)"
Selection.AutoFill Destination:=Range("AB2:AB272")
Range("AB2:AB272").Select

I want to replace AB272 with something that will automatically populate the number of rows in sheet 1. I have tried many things and had little luck getting it to work.

Please help!!

Hi

Find a column that will have an entry in the last row (say column A) then do something like

range("AB2").autofill destination:=range("AB2:AB" & cells(rows.count,"A").end(xlup).row)

rylo

thanks,

Maybe I am an idiot, Still not working. I have:

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Sheet2!C[-27]:C[-26],2,FALSE)"
Selection.Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row)

Hi

Put up an example workbook.

rylo

test.xlsAttachment 140648

thanks

Hi

Range("AB2").autofill...

not

selection.range("AB2").autofill....

Big assumption is that you are actually in AB2 when you put in the vlookup formula of course.

rylo

this gives a run time error stating that "autofill method of Range class failed"

Select ("AB2")
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Sheet2!C[-27]:C[-26],2,FALSE)"
Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "AB").End(xlUp).Row)

Hi

You are trying to use the column with the formula as the basis for extracting the last row. I opened your example file test.xls from post #5 and ran

  Range("AB2").Select
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],Sheet2!C[-27]:C[-26],2,FALSE)"
  Range("AB2").AutoFill Destination:=Range("AB2:AB" & Cells(Rows.Count, "A").End(xlUp).Row)

and go no errors.

rylo

thanks all is good. thanks so much