Vlookup till empty

[Ildestino]

Dear VBA Gurus,

I am dealing with several VLookups in my Report. I would like to set a VLookup from one worksheet to another. Currently, I am using this macro

Sub Vlookup_TestName()'
' VLOOKUPS
'


'
    Sheets("MRT Scor").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Auxiliaries!C[10]:C[11],2,FALSE)"
    Selection.AutoFill Destination:=Range("B2:B47916")
    Range("B2:B47916").Select
    Calculate
    Columns("B:B").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False


End Sub

It does work, but I am afraid, i would deal with more than 47916 rows per report. I would like to set up a Vlookup that should look up till the row is empty and paste the answer to the designated column.

I would be really happy and thankful for any hints or ideas.

Kind regards

Ildestino

 

[BarryL]

Sub Vlookup_TestName()'
' VLOOKUPS
'
Dim lr as Long
lr = Sheets("MRT Scor").Range("B" & Rows.Count).End(xlUp).Row
'
    Sheets("MRT Scor").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Auxiliaries!C[10]:C[11],2,FALSE)"
    Selection.AutoFill Destination:=Range("B2:B"& lr)
    Range("B2:B" & lr).Select
    Calculate
    Columns("B:B").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False


End Sub

 

[Ildestino]

Sub Vlookup_TestName()'
' VLOOKUPS
'
Dim lr as Long
lr = Sheets("MRT Scor").Range("B" & Rows.Count).End(xlUp).Row
'
    Sheets("MRT Scor").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],Auxiliaries!C[10]:C[11],2,FALSE)"
    Selection.AutoFill Destination:=Range("B2:B"& lr)
    Range("B2:B" & lr).Select
    Calculate
    Columns("B:B").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False


End Sub

Hey Barry,

thanks for your input. I was running your code and I got this error here:

Selection.AutoFill Destination:=Range("B2:B" & lr)

Run Time Error 1004

Autofill method of Range class failed

Any ideas?

Thank you very much for your input.

BR

Ildestino

 

[BarryL]

Sub Vlookup_TestName()'
' VLOOKUPS
'
Dim lr as Long
lr = Sheets("MRT Scor").Range("B" & Rows.Count).End(xlUp).Row
'
    Sheets("MRT Scor").Select
   Range("B2:B" & lr).FormulaR1C1 = "=VLOOKUP(RC[-1],Auxiliaries!C[10]:C[11],2,FALSE)"
    
    Range("B2:B" & lr).Select
    Calculate
    Columns("B:B").Select
    Selection.Copy
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False


End Sub