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]

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]

Thanks Jonmo. Yup, I need the formula. Need a trail for accounting manager. Had to fix it using a crude method:

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
    FixAddress = ShtName & CT
    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
  FixAddress = ShtName & B
  
  
  
End Function

 

[Jonmo1]

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]

This would be a better alternative:

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
  FixAddress = A
    
  
End Function

 

[Jeffrey Mahoney]

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]

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

 

[Jonmo1]

Here's an outside the box solution..

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 = Names("MyRange").RefersToLocal
End Sub

 

[RickXL]

Hi,

I hate to be left out ...

Have another idea.

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]

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)
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 example I used doesn't show the worse case scenario.

Thank you all