Vlookup VBA Alternative

[helpexcel]

Is there a quicker way to use vlookup with VBA. I have a lot of data that I would just use vlookup with, across multiple sheets. Hoping there's a way to speed things up. THANKS!

 

[offthelip]

Yes there is a much faster way of doing it and that is using variant arrays. The main reason this is faster is because VBA is very slow at accessing the worksheet, so the way to speed up VBA is to minimise the number of accesses to any of the worksheets. One thing to realise is that the time taken to load a singe cell into VBa is almost the same as the time taken to load a large range of data into VBa. So to speed up the process of looking for a match DON'T use the EXCEL functions operating on ranges on the worksheet , load all the data in memory usng variant arrays and then do the searching in memory . The code below does a very simple VLookup equivalent. I have two lists of data in column A and B I have the item I want to match against column A in Cell E1, it puts the results in F1
This code will be extremely fast even over 10000 rows.

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Searchfor = Cells(1, 5)
For i = 1 To lastrow
 If inarr(i, 1) = Searchfor Then
  Cells(1, 6) = inarr(i, 2)
  Exit For
 End If
Next i

Obviously without more details of your exact layout I can't give any more detailed help

 

[helpexcel]

THANKS! I have data on sheet 1 in Column B, with lookup values going in columns C - AJ. I want to lookup those values on sheet 2 starting in column A and pull values from column B and others. Do i need to use this code for each column, or can it be used for multiple columns? I'm assuming a fresh code is needed for each sheet that is being used to pull info.

 

[offthelip]

Having found the row number (i) from the line :
If inarr(i,1)=Searchfor
You can then use this value to use any of the data in columns C to AJ.
The really fast way of doing this is to load the entire sheet in to a variant array.
this code will loop round the data in column A of sheet 2 looking for matches on column B of sheet 1 and then loops through all the values on columns C to AJ of that row on sheet 1. ( it just uses them in a message box which is obviously not what you want, but it is just to show you how to do it)


e.g

Sub test2()
With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 36))
End With


With Worksheets("sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
search4 = Range(.Cells(1, 1), .Cells(lastrow2, 1))
End With


For i = 1 To lastrow2
 For j = 1 To lastrow
  If search4(j, 1) = inarr(i, 2) Then
     For k = 3 To 36
      ' do what ever you want to do with the values from C to AJ here
      MsgBox (inarr(i, k))
     Next k
   End If
 Next j
Next i


  


End Sub

 

[helpexcel]

Thanks. Sheet 1 is the sheet i'm filling, Sheet 2 has the data. Would that mean that i need to switch the codes above? Also, it runs through and errors out on MsgBox (inarri(i, k)). I tried to remove this line as i don't need the message boxes (even though its nice to see), but then it errors on the If search4 line.

 

[cerfani]

inarr I assume he means for you to check if the value is in the array, i dont think that function exists... you can loop the array and test equality for each item

 

[offthelip]

I can see why it is coming up with those errors : msgbox needs a string and I got the indices round the wrong way:
If the sheets are the other way round just swap sheet1 for sheet 2
try this:

Sub test()
Dim txt As String


With Worksheets("sheet1")
lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(.Cells(1, 1), .Cells(lastrow, 36))
End With




With Worksheets("sheet2")
lastrow2 = .Cells(Rows.Count, "A").End(xlUp).Row
search4 = Range(.Cells(1, 1), .Cells(lastrow2, 1))
End With




For i = 1 To lastrow
 For j = 1 To lastrow2
  If search4(j, 1) = inarr(i, 2) Then
     For k = 3 To 36
      ' do what ever you want to do with the values from C to AJ here
      txt = inarr(i, k)
      MsgBox (txt)
     Next k
   End If
 Next j
Next i




  




End Sub

 

[helpexcel]

Thanks again for your help!!! I'm still getting an error at If Search4. I think this is because the number of rows in the data set on sheet 2 is significantly higher than what's in sheet 1, so (J,1)<>(I,2). Or am i missing what this actually does?
Sheet 2 is the whole data dump. I advanced filtered column A on sheet 2 to remove all duplicates and put that list in column B on sheet 1. I'm then trying to pull some of the other data over using "vlookup"

 

[offthelip]

What is the error you are getting? also what are the values in lastrow and lastrow2, ,i ,j, search4(j,1), inarr(i,1)

I think this is because the number of rows in the data set on sheet 2 is significantly higher than what's in sheet 1, so (J,1)<>(I,2). Or am i missing what this actually does?

Yes you are missing what is happening ,. ther is a double loop the outside loop I = 1 to lastrow loops down the rows in inarr whic his the dat from sheet 1 ( in my code)
the inner loop ( j = 1 to lastrow2) loop down the rows in search4 which the data in sheet 2, so there is no expectation that lastrow2 bears any resemblance to lastrow , they can both be absol;utely anything up to the maximm rows in excel
The if statement compare the data on row i of sheet 1 to row j of sheet 2
So give us a chance tell me what the error is and the values. open up the locals windows and look at all variable , what values are in i ,j

Stop using Vlookup!!! Real programmers don't need it!!

 

[helpexcel]

I'm trying to stop, with your help i will!
I got it running, but it's just empty text boxes and i need to keep hitting OK. The only way to stop it is crt+alt+del, and nothing in the locals window. Just so i understand, is the first part of the code setting the data range or the lookup values? The only reason I ask is that has the columns listed. Since the report i have may be different, I did update the code with this
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
inarr = Range(.Cells(1, 1), .Cells(LastRow, LastColumn))