I would use the worksheet formula =match() instead (I bet it's quicker than
=countif()). I could use a formula like:

=isnumber(match(e2,'wh isbns'!a:a,0))

It would return TRUE if there's a match and False if there is no match.

In fact, I'd do it manually instead of using a macro.

But if I needed a macro, I wouldn't do the =match() in code, I'd populate the
range with formulas that did the work.

This puts the formula in, converts to values, removes the false's and changes
the true's to your text. (I would have lived with the true/false.

Option Explicit
Sub Testme()

Dim myRng As Range
Dim myLookupRng As Range

With Worksheets("all seg isbns")
Set myRng = .Range("E2", .Cells(.Rows.Count, "E").End(xlUp))
End With

With Worksheets("wh isbns")
Set myLookupRng = .Range("a:a")
End With

With myRng.Offset(0, 1)
.FormulaR1C1 _
= "=isnumber(match(rc[-1]," _
& myLookupRng.Address(external:=True, _
ReferenceStyle:=xlR1C1) _
& ",0))"

'application.calculate 'are you still in manual calc mode???

.Value = .Value

.Replace what:=False, _
replacement:="", _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False
.Replace what:=True, _
replacement:="WH Match Found", _
lookat:=xlWhole, _
searchorder:=xlByRows, _
MatchCase:=False

End With
End Sub




Ezra wrote:

I have a worksheet ("All SEG ISBNs") which is a long list of numbers in
Column E. I have another sheet "WH ISBNs") in the same workbook with a long
list of numbers in Column A. Is it possible to write a macro that will take
each one of the numbers on the "WH ISBNs" sheet, look for it on the "All SEG
ISBNs" sheet, and, if it finds a match on the "All SEG ISBNs" sheet, add the
text "WH Match Found" next in Column F on the "All SEG ISBNs" sheet--that is,
next to each of the matching numbers? There may be duplicate numbers in each
column on each sheet, and it's OK if the macro inserts "WH Match Found" next
to each occurrence. (By the way, both sheets have header rows.)

I've used the COUNTIF function to identifying matching numbers, but it seems
to take a very long time to calculate. If was hoping that a macro-based
version would be faster, and besdies, I'm hoping to do this same thing for
other sheets in the same workbook, and the calculation time for a
formula-based solution is just going to get worse and worse.

I've poked about and found various macros that seem to come close to what I
need, but frankly, I'm not knowledgeable enough to manipulate them well, and
my various attempt to loop and whatnot keep ending in disaster.

Any help would be much, much appreciated!

--

Dave Peterson