Sub comparebooks()

LookFName = "C:\My documents\Lookup.xls"
Set Lookbk = Workbooks.Open(Filename:=LookFName)
Set SearchRange = Lookbk.Sheets("Sheet1").Columns("G")

With Workbooks("A.xls").Sheets("Sheet1")
Sh1RowCount = 1
Sh2RowCount = 1
Do While .Range("A" & Sh1RowCount) < ""
SearchValue = .Range("A" & Sh1RowCount)
Set c = SearchRange.Find(what:=SearchValue, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
With Workbooks("A.xls").Sheets("Sheet2")
.Range("A" & Sh2RowCount) = SearchValue
Sh2RowCount = Sh2RowCount + 1
End With
Else
.Range("B" & Sh1RowCount) = c.Offset(0, 6)
End If
Sh1RowCount = Sh1RowCount + 1
Loop
End With
Lookbk.Close savechanges:=False
End Sub


"James8309" wrote:

Hi, everyone

I am trying to do this vlookup by VBA and having a lot of
difficulties.

1. I have two workbooks;
a. Workbook "A.xls"
b. Workbook "Lookup.xls" under C:\My documents\

2. Workbook "A", sheet1 contains the data in column A:A that I have to
look up in Workbook "Lookup", sheet1 column G and lookup result in
column M. Look up results will be in workbook "A", Sheet1, Column B:B.

- This would be in formulae = vlookup(A(n),'[Lookup.xls]Sheet1'$G:$M,
7,false) where (n) represents row number.

3. When number 2 is performed, in Workbook "A", sheet1, column B:B,
some of the result will return #N/A.

Q: How do I make those #N/A results cut out from sheet1 and pasted in
Sheet2 from Cell A3 automatically?

I tried to set lookup ranges and hook up with "do while true" but I
just can't get it.

If anyone can help, that would be great!

Thank you in advance.