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.

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.

This will work:
One thing I do is I name a range in your lookup spreadsheet. I call it
LookupRange. It makes the lookup easier.


Sub LookupSub()
Dim LURange As Range
Dim Res As Variant
Dim X As Double
Dim Y As Double
Dim DataArray(5000, 1) As Variant

Set LookupRng = Workbooks("Lookup.xlsx").Names("LookUpRange").Refe rsToRange
X = 1
Do While True
If Cells(X, 1).Value = Empty Then Exit Do
Res = Application.VLookup(Cells(X, 1), LookupRng, 7, False)
If Not (IsError(Res)) Then
MsgBox "Found " & Res
Else
Fnd = Fnd + 1
DataArray(Fnd, 1) = Cells(X, 1).Value
Rows(X & ":" & X).Select
Selection.Delete Shift:=xlUp
GoTo SkipIncrement
End If
X = X + 1
SkipIncrement:
Loop
If Fnd 0 Then
Sheets("Sheet2").Select
Range("A65000").End(xlUp).Select 'this is a row with data, this row +1
is empty!
X = ActiveCell.Row
For Y = 1 To Fnd
X = X + 1
Cells(X, 1).Value = DataArray(Y, 1)
Next
End If


End Sub

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?