Lookup List

[John]

Hi,

I need to run a bit of code that will run through a range in a workbook and
check if there are any values (strings) that do not appear in a checklist
(one column of data) in another open workbook. So far I happy with get it
to run through the first range cell by cell but I'm a bit stuck with the
best way of getting it to check the reference list in the other workbook.
Can anyone help?

The reference list is about 700 records long, just for your information and
the reason that I'd like it to be in a separate sheet is that I need to run
this procedure on a number of workbooks.

Thanks in advance.

John

 

[kkknie]

Try (untested):

Sub Test()

Dim rCheck As Range
Dim rValue As Range
Dim bFound As Boolean

For Each rValue In Sheets("Sheet1").Range("A1:A10")
bFound = False
For Each rCheck I
Workbooks("Checklist.xls").Sheets("Checksheet").Range("A1:A700")
If rCheck.Value = rValue.Value Then
bFound = True
Exit For
End If
Next
If bFound = False Then
MsgBox rValue.Value & " was not found."
End If
Next

End Sub

Substituing the checklist workbook, worksheet and range fo
Checklist.xls, Checksheet and A1:A700 and the sheet, range to check fo
Sheet1 and A1:A10.

 

[John]

Hi K!

Thanks very much for the code. I've ended up using the middle For..Each
code and it works perfectly.

Is it a very different kettle of fish if you're trying to access a closed
workbook?

Anyway, thanks again.

Best regards

John

 

[kkknie]

I believe you will have to open them first. Heres a link with
solution to move sheets from one workbook to another. You can probabl
adapt it to do what you want.

http://www.mrexcel.com/board2/viewtopic.php?t=83630

 

[John]

That's great K. Thanks for your help.

John