PDA

View Full Version : Solved: lookup function in VBA



mrlenoble
04-23-2010, 06:24 AM
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
04-23-2010, 07:15 AM
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
04-23-2010, 08:06 AM
Thanks for your quick reply, but what if both sheet are in different workbooks ?

mdmackillop
04-23-2010, 08:12 AM
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
04-23-2010, 08:23 AM
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))