Solved: lookup function in VBA

[mrlenoble]

I need a lookup function in VBA. The lookup from column A of sheet2 in column A of sheet1 giving the result of column B of sheet1 copied in column B of sheet2.

Thanks in advance and regards,

mrlenoble

[mbarron]

Sub vlookupVBA()
Dim shtB As Worksheet
Dim rShtA As Range, rShtB As Range, rCell As Range
Set rShtA = Sheets("sheet1").Range("a:b")
Set shtB = Sheets("sheet2")
Set rShtB = Range(shtB.Range("A2"), shtB.Range("a2").End(xlDown))

For Each rCell In rShtB
On Error Resume Next
rCell.Offset(, 1) = Application.WorksheetFunction.vlookup(rCell, rShtA, 2, 0)
If Err.Number > 0 Then
rCell.Offset(, 1) = "No Match"
Err.Clear
End If
Next

End Sub

[mrlenoble]

Thanks for your quick reply, but what if both sheet are in different workbooks ?

[mdmackillop]

Using Excel 2007, your code is finding the match, but then returning Error 9 - Subscript out of Range, which is overwriting the found value. Can't think why.

Here's an alternative method using Find

Sub vlookupVBA2()
Dim shtB As Worksheet
Dim rShtA As Range, rShtB As Range, rCell As Range
Dim c As Range
Set rShtA = Sheets("sheet1").Range("A:A")
Set shtB = Sheets("sheet2")
Set rShtB = Range(shtB.Range("A2"), shtB.Range("a2").End(xlDown))

For Each rCell In rShtB
Set c = rShtA.Find(rCell, lookat:=xlWhole)
If Not c Is Nothing Then rCell.Offset(, 1) = c.Offset(, 1)
Next

End Sub

[mbarron]

md,
I don't know why either, especially since I wrote it using 2007.


mrlenoble,
Use this part and then the lookup method of your choice.

Dim shtB As Worksheet
Dim rShtA As Range, rShtB As Range, rCell As Range

Dim wbOther As Workbook
Set wbOther = Workbooks("Book1")
Set rShtA = wbOther.Sheets("sheet1").Range("a:b")

Set shtB = Sheets("sheet2")
Set rShtB = Range(shtB.Range("A2"), shtB.Range("a2").End(xlDown))