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:
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?
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) & ")"
This question generated 16 answers. To proceed to the answers, click here.
This thread is current as of June 02, 2015.