Intersecting Named Ranges - One Common Range

B

bmoon

New Member
Joined
Sep 21, 2011
Messages
9
Can anyone shed some light on how to find the intersection of one named range with other named ranges?

My thought was to loop through the named ranges excluding the common one, and add them to an array. I haven't found how to do this.

Here is the code, including all my thoughts and attempts.

The section with, For Each num In Range("Qty") works. This is the formula I want to apply to all ranges that intersect with the dynamic 'Qty' range defined in a single column.

Code: 
Dim theName As Range
Sub TrueCost()
    Dim num As Variant, A As Variant, B As Variant, C As Variant
    'Dim theName As Range
    For Each Name In ThisWorkbook.Names
    rngNames (Name)
    Next
    'For Each num In Range("Qty")
        'num.Offset(1, -1).Value = (num.offset(0,1).value * range("G2") /_
100 + num.offset(0,1).value) / num.offset(0,0).value + range("H2") /_
 count(offset($D$2,0,0,count($A:$A),0))
        'A = (num.Offset(1, 1).Value * Range("G2").Value / 100 +_
 num.Offset(1, 1).Value) / num.Offset(1, 0).Value + Range("H2") /_
 Range("Qty").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
        'B = Range("Qty").Rows(num.Offset(1, 0, 0, Range("Qty").Count, 0))
        'C = Range("Qty").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
        'MsgBox "Value = " & A
    'Next
    
End Sub

Sub rngNames(theName)
    Dim R As Range
    Dim RN As Name
    Set RN = ThisWorkbook.Names(theName)
    On Error Resume Next
    Set R = RN.RefersToRange
    Debug.Print R
End Sub
Any assistance is greatly appreciated.

Best,

Brad
 
Last edited:

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
I have been playing with INTERSECT, but haven't been able to get what I am looking for. I don't know how to set it up within the code I have.
 
Upvote 0
I'm not really clear on what you are trying to achieve. Can you spell it out in more detail?
 
Upvote 0
Here is a sample format of what the data looks like and the defined ranges:

Excel 2007<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); text-align: center; color: rgb(22, 17, 32);"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="font-weight: bold;">Header1</td><td style="font-weight: bold;">Header2</td><td style="font-weight: bold;">Header3</td><td style="font-weight: bold;">Header4</td><td style="font-weight: bold;">Header5</td><td style="font-weight: bold;">Header6</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="background-color: rgb(184, 204, 228);">Range1</td><td style="">Range1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="background-color: rgb(184, 204, 228);">Range1</td><td style="">Range1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="background-color: rgb(184, 204, 228);">Range1</td><td style="">Range1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="background-color: rgb(184, 204, 228);">Range1</td><td style="">Range1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="">Range1</td><td style="background-color: rgb(184, 204, 228);">Range1</td><td style="">Range1</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right; background-color: rgb(184, 204, 228);"></td><td style="text-align: right;"></td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="background-color: rgb(184, 204, 228);">Range2</td><td style="">Range2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="background-color: rgb(184, 204, 228);">Range2</td><td style="">Range2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="background-color: rgb(184, 204, 228);">Range2</td><td style="">Range2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="background-color: rgb(184, 204, 228);">Range2</td><td style="">Range2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="">Range2</td><td style="background-color: rgb(184, 204, 228);">Range2</td><td style="">Range2</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right;"></td><td style="text-align: right; background-color: rgb(184, 204, 228);"></td><td style="text-align: right;"></td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="background-color: rgb(184, 204, 228);">Range3</td><td style="">Range3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="background-color: rgb(184, 204, 228);">Range3</td><td style="">Range3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">16</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="background-color: rgb(184, 204, 228);">Range3</td><td style="">Range3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">17</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="background-color: rgb(184, 204, 228);">Range3</td><td style="">Range3</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">18</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="">Range3</td><td style="background-color: rgb(184, 204, 228);">Range3</td><td style="">Range3</td></tr></tbody></table>
Sheet1​




The Blue column is the "common" named range I was referring to. This named range is "Qty" in the code.

So, the intent is to apply a formula to each cell in the common range that intersects with Range1, then repeat where the common range intersects with Range2, then Range3.... etc.

The code I have now, in the For Each loop, applies the formula for all cells with values in the common range. This skews results based on the average calculated in the formula.

BTW, I am running Excel 2007 on an XP x32 machine.

Thanks,

Brad
 
Upvote 0
I'm still not completely clear and I know you don't want to 'select' the ranges, but do either of these help?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test1()<br>    Intersect(Range("Qty"), Union(Range("Range1"), Range("Range2"), Range("Range3"))).Select<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Test2()<br>    <SPAN style="color:#00007F">Dim</SPAN> Nm <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> R <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Nm <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Names<br>        <SPAN style="color:#00007F">If</SPAN> Nm.Name <> "Qty" <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> R = Intersect(Range("Qty"), Range(Nm.Name))<br>            <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>            <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> R <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                R.Select<br>                MsgBox "Range selected"<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> Nm<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
bmoon said:
So, the intent is to apply a formula to each cell in the common range that intersects with Range1, then repeat where the common range intersects with Range2, then Range3.... etc.
Click to expand...
What is the formula?
 
Upvote 0
Rick Rothstein said:
What is the formula?
Click to expand...

I should have cleaned up the code I posted originally. The idea was to pass on everything I was working to help show the process. Seems to cause more confusion than anything else.

The formula is:

Code: 
num.Offset(1, -1).Value = (num.offset(0,1).value * range("G2") /_
 100 + num.offset(0,1).value) / num.offset(0,0).value + range("H2") /_
 count(offset($D$2,0,0,count($A:$A),0))

How is the formula relevant to finding the intersections of the ranges?

Thanks,

Brad
 
Upvote 0
bmoon said:
The formula is:

Code: 
num.Offset(1, -1).Value = (num.offset(0,1).value * range("G2") /_
 100 + num.offset(0,1).value) / num.offset(0,0).value + range("H2") /_
 count(offset($D$2,0,0,count($A:$A),0))

How is the formula relevant to finding the intersections of the ranges?
Click to expand...
I think I may be misunderstanding what you are trying to do. Earlier you said...

"So, the intent is to apply a formula to each cell in the common range that intersects with Range1, then repeat where the common range intersects with Range2, then Range3.... etc."

That sounded to me like you wanted to put a physical formula in the cells where the defined name Qty's range intersected the defined name ranges Range1, Range2 and Range3 for the table you posted. But in answer to my question to show me the formula (that you wanted to end up in the cells), you gave me a code line that appears to put calculated values in cells offset from the cells for the previously mentioned intersection. So I am confused as to what you are asking us here? Can you clarify this for us? Oh, and the code you posted (quoted above) seems to have a syntax error somewhere... when I delete the line continuation characters and try to put it all on one line, the statement shows in red indicating a syntax error.
 
Upvote 0
One of the worst notation decisions in the history of the world (IMO) is Excel's use of the space as an intersection operator.

The formula =NameOne NameTwo returns the range that is the intersection of NameOne and NameTwo.

In VBA
Code: 
MsgBox Range("NameOne NameTwo").Cells.Count
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

KlausW
  • Solved
Run VBA one after the other
Replies
8
Views
209
Joe4
Joe4
KlausW
  • Solved
Change destination between 2 sheets
Replies
2
Views
151
KlausW
KlausW
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
384
sspatriots
S
S
  • Solved
Need value to be the Table Column Header in lieu of Cell Address
Replies
1
Views
282
sspatriots
S
G
  • Solved
Format & Protect range of cells using VBA
Replies
7
Views
311
Gajendran Yadhav
G

Forum statistics

Threads
1,215,256
Messages
6,123,913
Members
449,132
Latest member
Rosie14

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top