#### Jeffrey Mahoney

##### Well-known Member
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:

Code:
``````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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Jonmo1

##### MrExcel MVP
yep, the .Address property returns just the cell ranges, no sheetname references.
But your formula would need the sheet names.
So you'd have to concatenate the sheet name in using Sht.Name

But let me ask, do you actually need the 'formula' in the cell, or do you really just need the resulting value in the cell?

If you just want the value, I'd skip the formula and just add the ranges directly in VBA

NewSht.range("G22").Value = Application.Sum(U)

#### Jeffrey Mahoney

##### Well-known Member
Thanks Jonmo. Yup, I need the formula. Need a trail for accounting manager. Had to fix it using a crude method:
Code:
``````Function FixAddress(CT As String, ShtName As String) As String
Dim X As Long
Dim B As String
Dim A As String

If InStr(CT, ",") = 0 Then
Exit Function
End If

For X = 1 To Len(CT)
A = Mid\$(CT, X, 1)
B = B & A
If A = "," Then
B = B & ShtName
End If
Next X

End Function``````

#### Jonmo1

##### MrExcel MVP
If you must have the formula, I'd skip the union part.
Since you've hard coded all the ranges anyway.
Just do that right into the formula

NewSht.range("G22") = "=sum(C3_Report!M24, C3_Report!M33:M34)"

#### Jeffrey Mahoney

##### Well-known Member
This would be a better alternative:
Code:
``````Function FixAddress(CT As String, ShtName As String) As String  Dim X As Long
Dim A As String
Dim C As String

C = ","

A = ShtName & CT
If InStr(CT, ",") > 0 Then
A = Replace(A, C, C & ShtName)
End If

End Function``````

#### Jeffrey Mahoney

##### Well-known Member
For me, the Union merges contiguous ranges together and creates a much smaller length formula and easier to understand. There are potentially hundreds of cells involved, but most of them are in a contiguous range. I'm evaluating row by row.

Thank you. I thought maybe there was an alternative to .Address; like maybe .fulladdress OR .AddressWithSheetName!!! ha

#### shg

##### MrExcel MVP
Code:
``NewSht.range("G22").Formula = "=sum(" & U.address([COLOR="#FF0000"]External:=True[/COLOR]) & ")"``

Last edited:

#### Jonmo1

##### MrExcel MVP
Here's an outside the box solution..

Code:
``````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"))
NewSht.Range("G22").Formula = Names("MyRange").RefersToLocal
End Sub``````

#### RickXL

##### MrExcel MVP
Hi,

I hate to be left out ...

Have another idea.

Code:
``````Sub test()

Dim Sht As Worksheet
Dim NewSht As Worksheet
Dim arr As Variant
Dim shtName As String

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

shtName = "'" & Sht.Name & "'!"

ReDim arr(1 To 2)
arr(1) = shtName & "M24"
arr(2) = shtName & "M33:M34"

NewSht.Range("G22") = "=SUM(" & Join(arr, ",") & ")"

End Sub``````

#### Jeffrey Mahoney

##### Well-known Member
To Shg: That didn't add the sheet name for each range

The cool thing about union is that it joins the ranges. When I evaluate each row, I'm adding another cell to U. When it returns the range, I don't need to combine contiguous ranges.

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)
=sum('C3 Report'!\$M\$4,'C3 Report'!\$M\$6,'C3 Report'!\$M\$12:\$M\$16,'C3 Report'!\$M\$23:\$M\$24)

The example I used doesn't show the worse case scenario.

Thank you all

Replies
1
Views
134
Replies
1
Views
291
Replies
16
Views
278
Replies
4
Views
531
Replies
2
Views
224

1,195,625
Messages
6,010,754
Members
441,568
Latest member
abbyabby

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

### Which adblocker are you using?

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

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