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

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").Ra nge("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.



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26694

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" wrote in message
...

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 In
Workbooks("Checklist.xls").Sheets("Checksheet").Ra nge("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 for
Checklist.xls, Checksheet and A1:A700 and the sheet, range to check for
Sheet1 and A1:A10.

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=266945

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



--
kkkni
-----------------------------------------------------------------------
kkknie's Profile: http://www.excelforum.com/member.php...nfo&userid=754
View this thread: http://www.excelforum.com/showthread.php?threadid=26694

That's great K. Thanks for your help.

John
"kkknie" wrote in message
...

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

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

K


--
kkknie
------------------------------------------------------------------------
kkknie's Profile:
http://www.excelforum.com/member.php...fo&userid=7543
View this thread: http://www.excelforum.com/showthread...hreadid=266945