I should have told you that you use foundCell in lieu of a range and cell
address when you want info from the cell.

Examples:-
foundCell.Value
foundCell.Address

The same for c but there are examples of that in the MsgBox.

--
Regards,

OssieMac


"OssieMac" wrote:

I suspect that you might be changing worksheets in the processing and not
changing back to the original sheet but that is only a guess. However, you
would be better to use code like the following to identify the Found/Not
found.

The space and underscore at the end of lines is simply the syntax used to
insert a break in what is otherwise a full line of code.

Feel free to get back to me if you are still having problems.

Sub FindnProcess()

Dim rngA As Range 'Range of cells in column A
Dim rngB As Range 'Range of cells in column B
Dim c As Range 'Each cell in column A
Dim foundCell As Range 'Cell in col B where found

With Sheets("Sheet1")
Set rngA = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))

Set rngB = .Range(.Cells(2, "B"), _
.Cells(.Rows.Count, "B").End(xlUp))
End With

For Each c In rngA
Set foundCell = rngB.Find(What:=c.Value, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not foundCell Is Nothing Then
'Value has been found.
'Insert your found code here
'In lieu of msgbox
MsgBox "Found " & c.Value
Else
'Value not found.
'Insert your found code here
'In lieu of msgbox
MsgBox "Not found " & c.Value
End If
Next c

End Sub

--
Regards,

OssieMac


"Steve C" wrote:

I have a spreadsheet with about 350 names in column A. These are to be
compared with a much longer list of names (about 3100) in col. B. My thought
process is to select the first person in Col A, then search col. B to see if
there is a match. If so, run some code based on the fact that there's a
match. If the name is not found in col. B, run some other code to deal with
that. Then move to the next cell in col. A and perform the search again.
This would continue for all the names in col. A.

When a match is not found in col. B, I'm using an error trap to deal with
that situation. The problem is that I'm getting a run-time error 91 ("Object
variable or With block variable not set") instead of the error-handler code
being executed. I do not have any With blocks in my code. My approach in my
code is based on the Edit Find command, where Excel searches only a
selected range of cells for a match.

Here is my code so far:

Sub CompareColumns()

Dim ColACount as Integer
Dim ColAAddress As String, ColAName As String, ColBRange as String

'Code here that determines number of names in Col. A (ColACount)
'Code here that names the range of cells in Col. B as ColBRange


Range("A1").Select

For x = 1 To ColACount

ColAAddress = ActiveCell.Address
ColAName = ActiveCell.Value


Range("ColBRange").Select 'selects all entries in col. B so that only that
range is searched

On Error GoTo DoThisInstead 'if name not found, run this code instead

Selection.Find(What:=ColAName, After:= _
ActiveCell, LookIn:=xlValues, LookAt:=xlWhole,
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate

'Run code here where match is found, then
Range(ColAAddress).Offset(1, 0).Select 'the next cell in col. A is
selected
GoTo Keepgoing

DoThisInstead:
'Run code here where match is not found, then
Range(ColAAddress).Offset(1, 0).Select 'the next cell in col. A is
selected

Keepgoing:
Next x
End Sub

The code runs smoothly for matches that are found. In addition, the error
handling code fires properly for the first entry in col. A, which has no col.
B match. But for subsequent non-matches, instead of the error handler code
running, I get the "Object variable or With block variable not set" error.
Again, I do not have any With blocks in my code. Very strange that it works
for the first non-match, but not for ones after that!

I'm sure there's a better way for me to compare the columns. Thanks for any
assistance you can give me.
--
Steve C