I was using this code to copy all rows with "NO MATCH TO ANY GES", "4-$" or
"CNO-$" in column A from the "All Records" sheet and copy the rows to the
"New Confirm" sheet. I've altered my sheets a bit and now need to have this
code look in Column H. I've changed the 1 to 8 but the code won't work.
This code worked fine for searching A, how would I alter it for H? I know it
is simple and I've tried so many variations but I can't seem to get it to
work properly.

Thank you,

Dim rng As Range, Cell As Range

Dim i As Long, Sh As Worksheet
With Worksheets("All Records")
Set rng = .Range(.Cells(1, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
i = 1

Set Sh = Worksheets("NEW CONFIRM REPORT")
For Each Cell In rng
If UCase(Trim(Cell.Value)) = "NO MATCH TO ANY GES" Or _
UCase(Trim(Cell.Value)) = "4-$" Or _
UCase(Trim(Cell.Value)) = "CNO-$" Then

Cell.EntireRow.Copy Sh.Cells(i, 1)
i = i + 1
End If

Change it to

Set rng = .Range(.Cells(1, 8), _
.Cells(Rows.Count, 8).End(xlUp))


Co

--
colofnatur
-----------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...fo&userid=3435
View this thread: http://www.excelforum.com/showthread.php?threadid=56073

For readability, and qualifying everything, you can use

Set rng = .Range(.Cells(1, "H"), _
..Cells(.Rows.Count, "H").End(xlUp))

if your original code had been given to you in that form, you would have
worked it out yourself <g


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"colofnature"
wrote in message
...

Change it to

Set rng = .Range(.Cells(1, 8), _
Cells(Rows.Count, 8).End(xlUp))


Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile:
http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=560733