SUM with dynamic range

I wrote a macro to find the last data line on a worksheet. Now I want to assign a SUM  function to the end of a column which sums the preceding values in the column even after people change the values in the preceding cells. The total cell has to be on the same column but the SUM doesn't recognize dynamic cell addresses. What can I do?
wchiangAsked:
Who is Participating?
 
vboukharCommented:
...look at my formula:
ActiveSheet.Cells(LastRow+1, 5).FormulaR1C1 = _
        "=SUM(INDIRECT(CONCATENATE(""E5:"",ADDRESS(" & LastRow & ",5))))"  - it works.
Good luck!
0
 
antratCommented:
Could you give an example based on what you want.
Thanks.
0
 
bruintjeCommented:
Hi wchiang,

Use the $ sign to lock in Columns or Cells. For example you could use $A50 or $A$50 to respectively lock your column values or lock you Cell values.
In the normal SUM function it is stated as A50.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wchiangAuthor Commented:
bruintje/antrat,
I apologize for not explaining myself clearly. In the macro, I created a variable called LastRow to hold an integer which changes from worksheet to worksheet. I need to somehow use LastRow to get the sum of the preceding rows in a particular column. I tried using a SUM function but it doesn't recognize what LastRow is. I could SUM the entire column but since the total has to be on the same column it generates a self-referential error. Let me know if you need more info.

Thanks,
William
0
 
wchiangAuthor Commented:
Here's an example of the SUM that didn't work:
SUM("E5:ELastRow")
SUM doesn't seem to recognize anything except literal rows and columns, so I was trying to find another way to get the total there.
0
 
antratCommented:
Hi Wchiang
Thanks for the example. Could you use the SUMIF argument ie,=SUMIF(E5:E2000,">1") The row E2000 could of course be any row number. Let me know if I'am on the right track.
 

Dave.
0
 
wchiangAuthor Commented:
antrat,

I see two problems here
1. the sum cell would have to be after e2000, preventing the requirement that it follow the last row of data.
2. if the sum cell were between e1 and e2000, it would produce a self referential error.

wchiang
0
 
antratCommented:
Wchiang
 
What about =if(E100="last row interger",sumif(E$1:E99,">1"),""))
 you could put this formula in the 2nd cell directly below your last row of data  and then copy it down are far as you need. the "last row of data" would of course be whatever the reference is for the last row of data.
0
 
antratCommented:
Wchiang
 
What about =if(E100="last row data",sumif(E$1:E99,">1"),""))
 you could put this formula in the 2nd cell directly below your last row of data  and then copy it down are far as you need. the "last row of data" would of course be whatever the reference is for the last row of data.
0
 
wchiangAuthor Commented:
antrat,
putting the formula into the worksheet resulted in a unrecognized variable -- LastRow -- error. However, I see that the same formula could work in the macro code but I would have to have several hundred lines of code just to find the correct cell address. I would need one line for each row on the worksheet up to the possible maximal row number. Any other suggestions?

wchiang

0
 
antratCommented:
Wchiang,
I Think I maybe didn't explain the last comment to well. When I said "E="Last row of data" I was assuming that in the cell directly below the last figure you had a formula in it for your macro to refer to , so "The last row of data" was meant for you to put whatever you had your macro refering to in its place.
So I'm going to make a few more assumptions lets say your column is"E" and your data at present goes from E1 to E100 meaning your last cell with a number in it is E100. In cell E101 try putting =if(and(E100>1,E102=""),sum(E1:E100),"")) then copy and paste the formula down as far as you need. If you already have formulas in the cells E1:Ewhatever for the numbers that you want to sum then try =if(and(E100>1,E102=your formula),sum(E1:E100),your formula)) "your formula" is whatever formula you have in the cells ,if any.
Let me know if I'm getting close , if not let me know if you are using a formula in any of the cells in E column and if your  "Last
row" macro is refering to a formula in any cells in E column.
0
 
vboukharCommented:
Why not create reference to range and use  INDIRECT - like that:
ActiveCell.FormulaR1C1 = _
        "=SUM(INDIRECT(CONCATENATE(""C1:"",ADDRESS(" & LastRow & ",3))))" - if you use VBA and LastRow is variable
or simple formula
"=SUM(INDIRECT(CONCATENATE(""C1:"",ADDRESS(LastRow,3))))" - if LastRow is named cell on your sheet.

0
 
wchiangAuthor Commented:
vboukhar/antrat,
well I finally found a roundabout way to get it to work thanks to your suggestions. What I did was add these lines to my macro:

ActiveSheet.Cells(1, 1).Value = "E5:E" & Right(Str(LastRow - 1), Len(Str(LastRow - 1)) - 1)
ActiveSheet.Cells(LastRow, 5).Formula = "=SUM(INDIRECT(A1))"
ActiveSheet.Cells(1, 1).Font.Color = RGB(255, 255, 255)

It still seems a bit clumsy because i couldn't get around the fact that I couldn't use LastRow in SUM without first assigning its value to another cell (and hiding it).

Thanks for your help! I hope you wouldn't mind sharing the credit. I'll wait another day before accepting your answers to see if you have further comments.

wchiang
0
 
vboukharCommented:
Maybe I misunderstood something, but why you tell that you  "couldn't use LastRow in SUM without first assigning its value to another cell" - look at my formula:
ActiveSheet.Cells(LastRow+1, 5).FormulaR1C1 = _
        "=SUM(INDIRECT(CONCATENATE(""E5:"",ADDRESS(" & LastRow & ",5))))"  - it works.
0
 
wchiangAuthor Commented:
vboukhar,
you're absolutely right. It works! I didn't copy the whole thing the first time because I didn't fully understand what it was doing, but I'll make sure I understand it now.

antrat,
thanks for your help but I'll have to give the points to vboukhar.

wchiang
0
 
wchiangAuthor Commented:
vboukhar,
please submit your answer again so i can accept it.

thanks,
wchiang
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.