Named Range ..Offset?

[Jagstrom]

have two named ranges "NameA" and "NameB"

When I loop though "A" and test for values how do I address the same row in "B"

Example

"NameA" "NameB"
1 text1
2 text2
3 text3

Something like:

For Each MyRange in Range("NameA")
if MyRange = 2 then
MyRange.Offset.Range("NameB") = "Changed text2"
next MyRange

I figure if use named ranges the end user can add and remove columns without breakn mycode

 

[SkipVought]

hi,

For Each MyRange in Range("NameA")
 if MyRange.value = 2 then 
    MyRange.Offset.(1,0).value = "Changed text2"  
 end if
next MyRange

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

I believe the arguments of offset were transposed. Should be:
MyRange.Offset.(0,1).value = "Changed text2"

 

[SkipVought]

touche, ep!

My dyslexia is showing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jagstrom]

Thanks for your input,

I guess I could have explained that better but NameA and NameB are not in adjacent columns.

The Offset needs to be tied to the relative difference between the two ranges where ever they may be.

 

[SkipVought]

dim iOff as integer
iOff = Range("NameB").column - Range("NameA").columnn
For Each MyRange in Range("NameA")
 if MyRange.value = 2 then 
    MyRange.Offset.(0,iOff).value = "Changed text2"  
 end if
next MyRange

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Jagstrom]

Clever,

I should have seen that one.

Thanks, Skip

Jason

 

[SkipVought]

I should have seen that one."

After you begin to understand the Excel Object Model better, these things can become more readily apparent.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}