Vlookup across workbooks

[tiptoe]

I'm using the following code to retrieve values from a second workbook
- the data is sorted in the second workbook. I've run the macro using
F8 and after a few successful retrievals within the loop, when a
target is not found in the second workbook the run time error 1004 is
reported.

Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Formula =
Application.WorksheetFunction.VLookup(Range("i" & n + 1), _
Workbooks(mshare).Sheets("Sheet1").Range("$A:$Z"), 7,
False)
Next n

If I omit the false condition the macro runs through but on
examination, where no match has been found, a result is taken from
another row.

I'd like to return a "Not in second workbook" where a match is not
found. Is this possible?

I'd appreciate any assistance,

Bob Golightly
Nottingham, UK

 

[Dave Peterson]

That's the way application.worksheetfunction.vlookup() works.

But application.vlookup() will return an error:

Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Application.VLookup(Range("i" & n + 1), _
Workbooks(mshare).Sheets("Sheet1").Range("$A:$Z"), 7, False)
Next n

I changed your .formula to .value.

Did you want to plop a formula into that cell or just the results?

 

[tiptoe]

That's the way application.worksheetfunction.vlookup() works.

But application.vlookup() will return an error:

Sheets("Hidden").Select
Range("j1").Select
For n = 1 To NumRows - 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Application.VLookup(Range("i" & n + 1), _
Workbooks(mshare).Sheets("Sheet1").Range("$A:$Z"), 7, False)
Next n

I changed your .formula to .value.

Did you want to plop a formula into that cell or just the results?

Dave,

Many thanks, it now runs returning #N/A in the cells without a match.
Can I be cheeky and ask you to suggest how I may replace the #N/A with
preferred test, e.g Not found ?

Bob