Most valuable professional
  • Hot Topics

Excel Address of Non-Contiguous Range

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

This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of June 2, 2015.

For more resources for Microsoft Excel:

This article includes the following tags:

  • Excel
  • Microsoft Excel