Range question

[drew23]

Hi

I am writing a code that looks through a range and looks for values that contain "ADR".

The range i am using is Range("D" & i).Select

My question is: How can i replace "D" with a counter or a variable, so that when it is done with column D it will move to the next column.

My code below.

endrange = Range("A65000").End(xlUp).Row
For i = 1 To endrange
mycheck = Range("d" & i).Value Like "*ADR*"
If mycheck = True Then
Range("D" & i).Select
Selection.Delete Shift:=xlToLeft

End If
Next i


Thanks

 

[namliam]

Look at the "Cells" way of doing things...

Cells( 1,1) = Range ("A1")

Good luck !

 

[drew23]

Namliam

I have tried that, but this than affects line
mycheck = Range("d" & i).Value Like "*ADR*"

for some reason when using the cell method it does not find any valued that contain adr.

It only works with a range method.

tks

 

[namliam]

Keep in mind that Cells is a little wierd (IMHO)
Cells(rwIndex, colIndex)

The first number is the Row, the second the Column

Cells(1,4) = Range("D1")

Cells works (other than above switch around of columns/rows) exactly the same as range, there is NO reason it should not work.

 

[boblarson]

Just to be explicit:

mycheck = Range("d" & i).Value Like "*ADR*"

would translate to

mycheck = Cells(i, 4).Value Like "*ADR*"

or, if you used another variable (say c)

mycheck = Cells(i, c).Value Like "*ADR*"

remember that the column and row are switched around in Cells than in Range. So, in Range you use Column and then Row, but in Cells you use Row and then Column.