Quick Question on Code

[JOUIOUI]

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

[colofnature]

Change it to

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


Col

[Bob Phillips]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>