Solved

SUM with dynamic range

Posted on 1999-01-28
16
446 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
  • 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 100 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel VBA - Merge / Borders / Bold 2 50
stop word moving down 2 40
Word - Access 3 29
Excel VB Help 4 39
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now