View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
JLGWhiz[_2_] JLGWhiz[_2_] is offline
external usenet poster
 
Posts: 1,565
Default Macro to Compare Two Columns of Numbers on Two Sheets and Flag

Although it worked in a test set up, it looks as though you have enough
suggestions to play with for a while, so I will move on.


"Ezra" wrote in message
...
I appreciate the code, JLGWhiz! Unfortunately, it seems to keep getting
stuck
in a loop somewhere. That is, it starts up and doesn't error out, but it
never stops running. I've made sure there aren't any blank cells in either
of
the columns being compared, but that doesn't seem to make a difference--it
just keeps on going, with the result that I have to (on a Mac) do a Force
Quit to pull out.

If you have any more ideas, I'd really appreciate it--certainly, I
appreciate what both you and Bernie (above) have contributed so far!

Ezra

"JLGWhiz" wrote:

Try this:

Sub matchemup()
Dim ws1 As Worksheet, ws2 As Worksheet, rng As Range
Dim lr As Long
Set ws1 = Worksheets("All SEG ISBNs")
Set ws2 = Worksheets("WH ISBNs")
lr = ws1.Cells(Rows.Count, 5).End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = ws1.Range("E2:E" & lr)
For Each c In rng
For i = 2 To lr2
If c.Value = ws2.Cells(i, 1).Value Then
c.Offset(, 1) = "WH Match Found"
End If
Next
Next
End Sub


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