Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SUM with dynamic range

Posted on 1999-01-28
16
Medium Priority
?
455 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

618 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