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.

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?

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.

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

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.

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.

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

What about =if(E100="last row interger",sumif(E$1:E99,">

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.

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.

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

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=""),su

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.

ActiveCell.FormulaR1C1 = _

"=SUM(INDIRECT(CONCATENATE

or simple formula

"=SUM(INDIRECT(CONCATENATE

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,

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

ActiveSheet.Cells(LastRow+

"=SUM(INDIRECT(CON

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.

ActiveSheet.Cells(LastRow+

"=SUM(INDIRECT(CON

Good luck!