Intersecting Named Ranges - One Common Range

[bmoon]

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.

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

 

[Rick Rothstein]

One of the worst notation decisions in the history of the world (IMO) is Excel's use of the space as an intersection operator.

In VBA

MsgBox Range("NameOne NameTwo").Cells.Count

Agreed, which is why I would opt to write that statement like this (as I think it would be clearer)...

MsgBox Intersect(Range("NameOne"), Range("NameTwo")).Cells.Count

 

[bmoon]

...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?

That is correct. I want the calculated values from the formula placed in cells offset from the cells of the previously mentioned intersection. I am using the values in the cells of each intersection within the formula; a seed, per se.

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.

I've confused myself too. Here is the code/formula that was working:

num.Offset(1, -1).Value = (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

When I test this now, it runs six times and throws, Run-time error '6': overflow.
As shown, the formula uses the entire named range "Qty". I will need to change that to only, the count of the intersection of the ranges. Peter's second section of code, in Reply #6, is close to what I am looking for. (Thank you Peter!) It selected the correct intersections. Now I just need the formula in place of ".select".

I hope this is clearer.

 

[bmoon]

I've been trying to modify what Peter showed in Post #6 as Test2.

Here is what I have:

Dim Nm As Object
Dim R As Range
Dim num As Variant, b As Integer

For Each Nm In ThisWorkbook.Names
If Nm.Name <> "Qty" Then
On Error Resume Next
Set R = Intersect(Range("Qty"), Range(Nm.Name))
ThisWorkbook.Names.Add Name:="R", RefersTo:=R, Visible:=False
On Error GoTo 0
If Not R Is Nothing Then
'For Each num In R
'num.Offset(1, -1).Value = (num.Offset(1, 1).Value * Range("G2").Value /_
'100 + num.Offset(1, 1).Value) / num.Offset(1, 0).Value + Range("H2") /_
'Range("R").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
'Next
'R.Select
b = Range("R").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox "Range selected: " & R.Address
MsgBox b
End If
End If
Next Nm​

At first, when I received the Method 'Range' of 'object' _Global Failed error, I went searching and found the following post:

http://www.ozgrid.com/forum/showthread.php?t=35336&page=1

From which I deduced that range "R" needed to be named as such in the workbook. So, I added it:

ThisWorkbook.Names.Add Name:="R", RefersTo:=R, Visible:=False​

I commented the For Each loop and created variable "b" in an attempt to isolate where I thought the code is failing in the formula. The same error is thrown where "b" is defined.

What does this error mean?

Thank you,

Brad

 

[Rick Rothstein]

Here is what I have:

Dim Nm As Object
Dim R As Range
Dim num As Variant, b As Integer

For Each Nm In ThisWorkbook.Names
If Nm.Name <> "Qty" Then
On Error Resume Next
Set R = Intersect(Range("Qty"), Range(Nm.Name))
ThisWorkbook.Names.Add Name:="R", RefersTo:=R, Visible:=False
On Error GoTo 0
If Not R Is Nothing Then
'For Each num In R
'num.Offset(1, -1).Value = (num.Offset(1, 1).Value * Range("G2").Value /_
'100 + num.Offset(1, 1).Value) / num.Offset(1, 0).Value + Range("H2") /_
'Range("R").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
'Next
'R.Select
b = Range("R").Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox "Range selected: " & R.Address
MsgBox b
End If
End If
Next Nm
​

At first, when I received the Method 'Range' of 'object' _Global Failed error, I went searching and found the following post:

You declared R as a Range... the "R" inside the Range call is not that R, it is the text character "R" because it is surrounded by quote marks. BUT, since R is already a range, you should not be putting it inside a Range call anyway... just use it as is (it is already a Range).

b = [SIZE=3][COLOR=red]R[/COLOR][/SIZE].Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count

 

[bmoon]

b = [SIZE=3][COLOR=red]R[/COLOR][/SIZE].Offset(1, 0).Cells.SpecialCells(xlCellTypeConstants).Count

Excellent. Thank you Rick.

Now, I still have that 'Overflow' error I mentioned in thread #12. Any thoughts on that?
---------------------------------------------------------
I just found the error. There must have been a division by zero or something with the my row offset in the formula. I set the row offset to zero and everything works!

Thank you everyone!