VBA VLOOKUP Help!

[MurraySMTH]

Hi guys, I need to incorporate a Vlookup code into VBA, I am really new to VBA, so any help is appreciated.

=VLOOKUP(B5,Sheet2!A:B,2,FALSE) this is my Vlook up code, and this is connected to this formula =IF(D5=E5,"Match","Mismatch") so that will need to be attached as well.

Please Help!!!

 

[alansidman]

This should direct you to a suitable solution

Calling Worksheet Functions In VBA

www.cpearson.com

 

[MurraySMTH]

I put this in...

Sub Vlookup()



Dim Res As Variant
On Error Resume Next
Err.Clear
Res = Application.WorksheetFunction.Vlookup(B5, Range("Sheet2!A:B"), 2, False)
If Err.Number = 0 Then
''''''''''''''''''''''''''''''''
' Value was found. Continue normal code execution
''''''''''''''''''''''''''''''''
Else
''''''''''''''''''''''''''''''''
' Value was not found. Error code goes here.
''''''''''''''''''''''''''''''''
End If
End Sub



But still not getting a result.

 

[offthelip]

What you have put in your code as "B5" implies that B5 is a variable with a name B5 and NOT the value in cell B5, one quick way round this is to load the value in the cell B5 into your variable called B5.
so add this line of code before your vlookup

B5 = Range("B5:B5")
res = Application.WorksheetFunction.VLookup(B5, Range("Sheet2!A:B"), 2, False)

 

[MurraySMTH]

Would i have to put the cell of where it needs to put the result?

Such as E5?

 

[offthelip]

If you want to write the result out to a cell then you need specifically address the cell using either a cell statement or a range statement, The results from your code is currently sitting in the variable "res"
You can do what you like with it. e.g throw a msgbox with:

msgbox (res)

or write it to cell E5 with either of the following statements

Range ("E5:E5")=res

or

cells(5,5)=res

With this last addressing mode it is done number with rows FIRST and column number second, so D5 would be cells (5,4)

 

[MurraySMTH]

It worked!! You are the best. Thank you for being so patient with me. Any chance you know how to fill down visable cells? That is my next challenge.

 

[offthelip]

this code will loop round all rows from row 5 to the last row with data in it.
Note I have used Variant arrays to load all the data from the worksheet in one go and then write all the data back to the worksheet in one go. The reason for this is VBA can be very fast, but one of the main reasons for slow VBA is because it takes a comparatively long time to access the worksheet to read or write to or from it. SO it is much more efficient to read all the data once and write all the data once, often more than 1000 times faster. For a few rows it doesn't matter but for a few thousand it makes the difference between milliseconds and seconds.

Sub VlookupVBA()
' It is NOT good practive to call your sub routine the same name as one of the eXCEL functions it causes confusion
' So I have changed the name of your subroutine
Dim Res As Variant
On Error Resume Next
Err.Clear
'Find the last value in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
' load all of column B into a varaint array
colb = Range(Cells(1, 2), Cells(lastrow, 2))
' load output array from column E
cole = Range(Cells(1, 5), Cells(lastrow, 5))
' loop through all the rows
For i = 5 To lastrow
B5 = colb(i, 1)
Err.Clear
Res = Application.WorksheetFunction.Vlookup(B5, Range("Sheet2!A:B"), 2, False)
If Err.Number <> 0 Then
''''''''''''''''''''''''''''''''
' Value was not found. Error code goes here.
''''''''''''''''''''''''''''''''
Res = "Value not found"
End If
cole(i, 1) = Res
Next i
' Write output array to column E
Range(Cells(1, 5), Cells(lastrow, 5)) = cole


End Sub