Vba & vlookup

[Robotacha2010]

Hello,

Haven't poste here in a while.

Could someone help me out with a little problem I am having.

I have written / Recorded a macro, which VLOOKUP's data from another workbook.

The amount of ROWS on each workbook changes on a daily basis. (anthing from 5 rows upto 1000+ rows)

The macro I have works fine apart from this bit.

Range("O1").Select
    ActiveCell.FormulaR1C1 = "Availability"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[availability (worst offenders).xls]Sheet1'!R2C2:R1005C6,5,FALSE)"
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-12],'[availability (worst offenders).xls]Sheet1'!R2C2:R1005C6,5,FALSE)/100"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("O2:O145")
    Range("O2:O145").Select

in cell O2, I VLOOKUP the data, then doubleclick the bottom right corner of the cell to autofill down to the bottom of the list.

Of course, with the amount of rows changing on a daily basis, I need to autofill down to the bottom of the list, but I dont know how many rows there is going to be.

The current VBA code shows range O2:O145, which is the amount of rows that were there when I first recorded the macro.


Can anyone help please? :D

Any questions just ask, because I probably havent explained it that clearly.


Thanks in Advance :D

[StephenR]

Perhaps this. The last row is based here on column A so just change as appropriate. Have removed redundant select statements too.

Sub x()

Dim r As Long

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

Range("O1").Value = "Availability"
Range("O2").FormulaR1C1 = _
    "=VLOOKUP(RC[-12],'[availability (worst offenders).xls]Sheet1'!R2C2:R1005C6,5,FALSE)/100"
Range("O2").AutoFill Destination:=Range("O2:O" & r)
    
End Sub

[Robotacha2010]

that worked a treat.

thankyou greatly!

And thank-you for removing the irrelevant code.

I am still using MACRO RECORDER a hell of a lot, but I am learning quickly


best regards.