Searching a Column

[ToddG]

I need to search a Column for any cell that is

1. Not Blank
2. Not equal to "Size"
3. Not equal to "Grand Total"

Then select the TWO cells to its left.

 

[Tod]

You could do something like this:

'Assuming Column C is the one you are searching
'and has a heading.
Set SearchRange = Range("C2:C" & Range("C65536").End
(xlUp).Row)
For Each Cell In SearchRange
If Len(Cell.Value) > 0 Then
If Cell.Value <> "Size" And _
Cell.Value <> "Grand Total" Then
Range(Cell.Offset(0, -2).Address & ":" &
Cell.Offset(0, -1).Address).Select
'Code to do what you're going to do to the two
cells.
End If
End If
Next Cell


Tod

 

[ToddG]

Hi Tod,

Thank for your reply. I modified your code a little to do
something a little differently and it works fine. I
modified it to select some different cells from what I
asked to begin with and copy them. One thing I would like
it to do is to copy ALL of the cells that meet
our "search" criteria. Currently it copies the LAST cells
in the range that meet the "search" criteria.

The new code:

Sub TEST2()

Set SearchRange = Range("F2:F" & Range("F65536").End
(xlUp).Row)
For Each Cell In SearchRange
If Len(Cell.Value) > 0 And _
Cell.Value <> "Size" And _
Cell.Value <> "Grand Total" Then
If Cell.Font.Bold = True Then
Range(Cell.Offset(0, 0).Address & ":" &
Cell.Offset(0, -1).Address).Copy
End If
End If
Next Cell

End Sub

Any advice would be appreciated,

Todd

 

[tod]

You'll need to do something with the cells that you've copied before
your code cycles to the next record. Each time the code loops, it'll
replace what you've copied from the last time.

Range(Cell.Offset(0, 0).Address & ":" & Cell.Offset(0,
-1).Address).Copy
'code to do something with copied cells

tod