Address of Non-Contiguous Range

[Jeffrey Mahoney]

I have some routines where I want to use the address of cells on another sheet as reference for a Sum formula in VBA. Here is a simple example:

Dim U as range
Dim Sht as worksheet
Dim NewSht as worksheet

Set Sht as Sheets("C3_Report")
Set NewSht = Sheets("Income Expense Summary")

Set U = Union(Sht.range("M24"),Sht.range("M33"),Sht.range("M34"))
NewSht.range("G22") = "=sum(" & U.address(1,1) & ")"

The result for Cell G22 on C3_Expense_report sheet is: =SUM('C3_Report'!$M$24,$M$33:$M$34)
This gives me the sum of cell M24 on C3_Report plus the sum of cells M33 and M34 on Income Expense Summary.
I need it to be like this: =SUM('C3_Report'!$M$24,'C3_Report'!$M$33:$M$34)

I created a roundabout way to fix it, but maybe there is an easier way. Any suggestions?

 

[Jonmo1]

To
Any examples of adding the sheet name during the evaluation process seems to create a very long formula. Potentially:
=sum('C3 Report'!$M$4,'C3 Report'!$M$6,'C3 Report'!$M$12,'C3 Report'!$M$13,'C3 Report'!$M$14,'C3 Report'!$M$15,'C3 Report'!$M$16,'C3 Report'!$M$23,'C3 Report'!$M$24)
instead of this:
=sum('C3 Report'!$M$4,'C3 Report'!$M$6,'C3 Report'!$M$12:$M$16,'C3 Report'!$M$23:$M$24)

The macro in post #8 results in this formula in G22 on the Income Expense Summary Sheet.
=SUM('C3_Report'!$M$24,'C3_Report'!$M$33:$M$34)

 

[shg]

To Shg: That didn't add the sheet name for each range

It adds the sheet name for the first range string, which is sufficient: by definition, all cells in a range are on the same sheet.

 

[Jeffrey Mahoney]

To shg: This formula: =sum('C3_Report'!$M$24,$M$33:$M$34) produces a result that is the sum of one cell on C3_Report and two cells on the current sheet (Income Expense Summary)

JonMo: I'll try what you're proposing. I didn't understand what you were doing and skipped over.

 

[Jeffrey Mahoney]

Oh! I see. You're creating a new named range for each Union. Creative! So if the accounting manager wanted to see the values behind each formula, she would have to select the named range. I would also have to do some Named Range cleanup before running the report again. Something to think about. Clean Formulas.

I got an error on the last line: NewSht.Range("G22").Formula = Names("MyRange").RefersToLocal
Also, when I try to find the named range it created, it is not on the list. I'm a little confused with adding a named range with sum formula as in this line:Sht.Names.Add Name:="MyRange", RefersTo:="=sum(" & U.Address(1, 1) & ")". That line ran fine, but what happened?

 

[shg]

=sum('C3_Report'!$M$24,$M$33:$M$34) produces a result that is the sum of one cell on C3_Report and two cells on the current sheet

Ah, I see what you mean. Pondering.

 

[Jonmo1]

Oh! I see. You're creating a new named range for each Union. Creative! So if the accounting manager wanted to see the values behind each formula, she would have to select the named range. I would also have to do some Named Range cleanup before running the report again. Something to think about. Clean Formulas.

I don't know about 'each' union, there was no mention about a loop doing multiple unions. My code just did 1 union, as did your original post.
Anyway, yes. The idea is to create a named range scoped to the worksheet that the formula refers to.
It's pretty cool.
If you do it by hand, and enter the formula without sheet references, the name manager automatically adds the sheet references for you.
Then you can pull that formula from the Name into a cell.

This does assume that the formula you build will have references to only 1 sheet.
It won't work if the formula references multiple sheets like
Sum(Sheet1!A1, Sheet25!A1)

As far as 'cleaning up' and seeing the values. Your manager just needs to look at the formula in the cell, not the Name.
And we can easily add a line to delete the Name when it's done.
You probably don't see the name in the manager because you have it set to look at names scoped to the workbook.
The name created by the code is scoped to the worksheet, not the book.

The error may be because your code is in a sheet module, and I didn't qualify the sheet on the name for that line.


Try this revised version

Sub test()
Dim U As Range
Dim Sht As Worksheet
Dim NewSht As Worksheet

Set Sht = Sheets("C3_Report")
Set NewSht = Sheets("Income Expense Summary")

Set U = Union(Sht.Range("M24"), Sht.Range("M33"), Sht.Range("M34"))
Sht.Names.Add Name:="MyRange", RefersTo:="=sum(" & U.Address(1, 1) & ")"
NewSht.Range("G22").Formula = Sht.Names("MyRange").RefersToLocal
Sht.Names("MyRange").Delete
End Sub