Best Way Question
 

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

Best Way Question
 

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


"Ray Batig" wrote in message
nk.net...
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

Best Way Question
 

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


--
Don Guillett
SalesAid Software

"Ray Batig" wrote in message
nk.net...
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