?
Solved

SUM with dynamic range

Posted on 1999-01-28
16
Medium Priority
?
453 Views
Last Modified: 2010-08-05
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?
0
Comment
Question by:wchiang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 3
  • +1
16 Comments
 
LVL 9

Expert Comment

by:antrat
ID: 1616058
Could you give an example based on what you want.
Thanks.
0
 
LVL 44

Expert Comment

by:bruintje
ID: 1616059
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
 

Author Comment

by:wchiang
ID: 1616060
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:wchiang
ID: 1616061
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
 
LVL 9

Expert Comment

by:antrat
ID: 1616062
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
 

Author Comment

by:wchiang
ID: 1616063
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
 
LVL 9

Expert Comment

by:antrat
ID: 1616064
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
 
LVL 9

Expert Comment

by:antrat
ID: 1616065
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
 

Author Comment

by:wchiang
ID: 1616066
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
 
LVL 9

Expert Comment

by:antrat
ID: 1616067
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
 
LVL 5

Expert Comment

by:vboukhar
ID: 1616068
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
 

Author Comment

by:wchiang
ID: 1616069
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
 
LVL 5

Expert Comment

by:vboukhar
ID: 1616070
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
 

Author Comment

by:wchiang
ID: 1616071
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
 

Author Comment

by:wchiang
ID: 1616072
vboukhar,
please submit your answer again so i can accept it.

thanks,
wchiang
0
 
LVL 5

Accepted Solution

by:
vboukhar earned 400 total points
ID: 1616073
...look at my formula:
ActiveSheet.Cells(LastRow+1, 5).FormulaR1C1 = _
        "=SUM(INDIRECT(CONCATENATE(""E5:"",ADDRESS(" & LastRow & ",5))))"  - it works.
Good luck!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
New style of hardware planning for Microsoft Exchange server.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question