Best Way Question

[Ray Batig]

I need some advice and help to solve this problem. There have to be several
ways, however, I just can't seem to get anything to work.

Here is what I have in a worksheet:

A B C D
1
2
3
4 txt1
5 txt2
6 txt3

Cells B4:B6 are in range MyRange

What I am trying to do is compare TestText to values in MyRange. When the
values match, ( i.e. TestText = txt2) I want to write a second
variable,Result, in the same row as the match (5 in this case) and in
column D. This is the core of a comparison test to fill in the column D
blanks based on a list of TestText values.

I tried this:

For each cell in Range("MyRange")
If TestText = Range.Cells.Value Then
Worksheets("MyWorkSheet").Cells( Range.Cells.Row.Value,4) =
Result
Endif
Next

I am thinking that Range.Cells.Row.Value should equal 5 for the example.

I can't get this to work so obviously I do not understand. How can this be
corrected or rewritten to meet my needs?

Thanks in advance for your help, and Merry Christmas.

Ray

 

[Doug Glancy]

Ray,

Assuming txt1, etc., are in column A, then this should work. (I changed the
range name to "cel" to avoid confusion with the reserved word "Cells":

For each cel in Range("MyRange")
If TestText = cel.Value Then
cel.offset(0,3) = Result
Endif
Next

hth,

Doug

 

[Don Guillett]

try this
For each cell in Range("MyRange")
If TestText = Cell Then cell.offset(,4)=Result
Next