Excel Ranges in VBA: Reference same row of NamedRange2 based on current location within NamedRange1 1

[kjv1611]

I must be forgetting something, but I figured I'd post online to see if any of you hear can clear the mud off my eyes.

For a process used by various Excel files, I want to say something like:

When they update a cell within Range1, a query runs, populating various cells. A new item I'm adding is WAY to the right, and I do not want to use the .Offset() structure to get there, but would rather name the range for Range2 (further right 1 column).

The data will look something like the below image:

It doesn't seem to me like using Range.Intersect nor Range.Offset would work.

How do I basically say, When the Target range (ActiveCell) is in Range1(3) or Range1, row 3, then update the value in Range2(3) or Range2, row 3?

Please embarrass me and show me what simple item I'm forgetting.

Thanks

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Hi,

You could use the Worksheet_Change Event.

If Not Intersect(Target, Range1) Is Nothing Then
   Cells(Target.Row, Range2.Column).Value = [i]KJV1611Value[/i]
End If

...assuming that Range1 and Range2 are defined as range objects as in a Structured Table (of course a ST would need some extra window dressing code)

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[kjv1611]

Yes, that's EXACTLY how I need to do this.

The data is not in a table object, but it is structured in basic column/row table design. So yes, it'll work in that method perfectly well.

I think I did consider that option at one point, but must have been when I had stepped away and forgot to test it or similar out.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

And, yes, Intersect can work as well...

Intersect(Target.EntireRow, Range2.EntireColumn).Value = [i]KVJ1611Value[/i]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[kjv1611]

That's pretty cool! Never thought of using Intersect that way. To date, I've only really used it to say "is RangeX within NamedRangeY?"

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57