VBA - Dynamic Vlookup

[GreatOffender]

Yes I have searched and honestly could not find the answer I was looking for as most questions were more complex. I am running 4 vlookups in a macro using basic FormulaR1C1.

The columns being searched are A:S on Sheet1 and the vlookups are being done on Sheet2 with the value being searched in column A of Sheet2 (A2) it would look like:
=vlookup(A2,A100:S100,4,0)

Problem I have, besides the clunky R1C1 code, is my data will always be changing in size. One day it may be 1100 rows ans the next 1500. I have read several solutions that get me close but never the final answer. This macro is more of a template used over and over but never to be saved. Data goes in, macro is run and data is transferred to a final report. Macro is closed without saving.

Thank you,

 

[rlv01]

You say you are using VLookup in a macro. You should probably post the macro code so it can be evaluated.

 

[offthelip]

A much faster way of doing a Vlookup in VBA is to use the Dictionary object, which is much much better for doing multiple lookup with the same variable.
This code should do what you want. It is generice code i use all the time when I want to do a vlookup in vba

Sub dictionarylookup()
' this shows hte use of a dictionary to copy a value to a mathcing worksheet
   Dim Ary As Variant
   Dim i As Long
   Dim Dic As Object
   Dim Cl As Range
   LastCol = 19
   Set Dic = CreateObject("Scripting.dictionary")
   With ActiveWorkbook.Sheets("sheet1")
   LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
   Ary = .Range(.Cells(1, 1), .Cells(LastRow, LastCol))
   End With
   For i = 1 To UBound(Ary)
      Dic(Ary(i, 1)) = Ary(i, 2)
   Next i
   With ActiveWorkbook.Sheets("Sheet2")
   LastRow2 = .Cells(Rows.Count, "A").End(xlUp).Row
   ary2 = .Range(.Cells(1, 1), .Cells(LastRow2, 1))
      For i = 1 To UBound(ary2)
         .Range(.Cells(i, 2), .Cells(i, 2)).Value = Dic(ary2(i, 1)) 'this matches the value given by tyhe index C1.value in the dictionary
      Next i
   End With
End Sub

note this code is only copying column B at the moment but can be altered to copy multiple columns or any other single column

 

[GreatOffender]

I'll give it a try. Thank you!