On Jul 1, 9:09*pm, Joel wrote:
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.- Hide quoted text -

- Show quoted text -

Thank you so much for your help. It does search, copy paste into
sheet2.
My search values are "Numbers stored in text" i.e. 0290099009 and when
it copies into sheet2, the format just changes as normal and displays
290099009. Is there anyway I can make the format same as well?