Is there a way to determine absolute cell reference while in a named range?

Question

I have two named ranges that overlap one another. While I'm looping down the rows of Range1 (A3:Z20), I need to also loop across the columns of a smaller portion of Range1, so I created Range2 (G1:K20).

What I'm looking for in looping through Range2 is if there is an "X" in any of the columns of the current row of Range1, I need to get the column's heading, which is why Range1 ignores rows 1 and 2, but Range2 does not.

My code sort of looks like this:

For intRowCntr = 1 To Range("Range1").Rows.Count
    For intColCntr = 1 To Range("Range2").Columns.Count
        If Range("Range1").Cells(intRowCntr, %intColCntr%) = "X" Then
            'Do a bunch of stuff here.
        End If
    Next intColCntr
Next intRowCntr

I marked the %intColCntr% with % to point out where I need the magic to happen. Obviously the %intColCntr% won't work the way it is because it'll be column 1 in Range2 but column 7 in Range1.

This is as simple as I can think to make an example and hopefully someone can see what I'm getting at. Basically I need to know where I am in relation to Range1, while looping through Range2. An acceptable alternative is knowing where I am on the Worksheet itself in both Range1 and Range2, and then working out the details on the worksheet level, before returning to the range loops.

Any advice would be appreciated!

:EDIT: So I guess I didn't explain well enough, or maybe what I'm doing is overly complicated for what my goal is, so here's more of the story:

I'm trying to populate several listboxes of options that apply to a product.

So imagine Range1 is a list of cars, and Range2, Range3, Range4... are all groups of options that COULD apply to every car in Range1. So Range2 would be colors, Black, White, Red, Yellow, etc... Range3 might be Trim options. Range4 might be body styles, and so on.

Now, I need the user to pick a car (Dodge Dart on Row 5). Now I need to go through Range2 looking for an "X" that denotes which color choices are available to a Dodge Dart. If I find an "X", I need to then grab that column's heading and drop it into a "Colors" listbox control.

I obviously can't just hit every column in Range1 looking for an "X" as every option group has it's own Listbox.

With a bigger picture of what I'm trying to do, is there some way of using Intersect that would make more sense?

Column would work, but I think it would require me to keep track of another set of variables that apply to locations on the Worksheet rather than just the two ranges. It would work but it would be fairly clumsy I think.

I'm not exactly clear, but I think the Intersect property, which returns a range that's the intersection of other ranges, might be of use. Also, the Column property returns a cell's column number within the entire sheet. — Doug Glancy
I updated my description to better show what the point of it all is, thinking maybe I was too abstract? Anyway, using Intersect could work. My issue then would be ensuring I'm on the appropriate row of Range1. I'll play with Intersect and let you know how it goes. — Charles B Hamlyn

L42 L42 · Accepted Answer · 2014-03-17T06:26:53

Like Doug, I'm not entirely sure what you want to do:
And like Doug, I'm looking on Intersect Function.
So try this:

Dim cel As Range

For Each cel in Range("Range1")
    If Not Intersect(cel, Range("Range2")) Is Nothing Then
        If cel.value = "X" Then
            'do your stuff here
        End If
    End If
Next

I used For Each Lopp rather than your conventional For-Next Loop so I can easily use the Intersect.
Hope this helps you a bit or maybe solve your problem.

Version2 of above:

Dim cel As Range

For Each cel in Intersect(Range("Range1"),Range("Range2"))
    If cel.value = "X" Then
        'do your stuff here
    End If
Next

Is there a way to determine absolute cell reference while in a named range?

2 Answers