Insert Formula with Variable Range

[excel6000]

I'm trying to input a simple formula via VBA to sum data which has a variable range. I need something like =sum(variable range).

Column A Column B
1 Example 1 15
2 Example 2 20
3 Example 3 12
4 Example 4 5
5 Total: =sum(B1:B4)

This is what I am looking for; but my problem is that range B1:B4 might actual be B1:B10 depending on which project I'm in.

[skywriter]

Duplicate post.

[StuCram]

Try this:

Please Login or Register  to view this content.

[excel6000]

I apologize for the duplicate threads. For some reason, my posts are not showing up under "My Posts".

[excel6000]

StuCram, Thank you. That did work; however, what if I have multiple groups of areas which need to be summed in that same column? For example, I have a group of data from B1:B4 which needs to be summed. Then I have a group of data from B6:B18 which needs to be added. My spreadsheet has several groups of data which need to be summed. And each group can change with the amount of rows it contains. I hope this makes sense.

[excel6000]

I've been trying the following, but it's giving me an error saying "Application-defined or object-defined error". If I could get this code to work, I would simply write the code to navigate to the cell where I want the "ActiveCell" to be and then let it work from there.

Please Login or Register  to view this content.

[skywriter]

Maybe this.
You can see it work in the workbook at the bottom of this post.

Please Login or Register  to view this content.

[excel6000]

I finally worked one out on my own!! This is what I have and it appears to work great so far.

Please Login or Register  to view this content.

[skywriter]

I finally worked one out on my own!! This is what I have and it appears to work great so far.

I don't see how this solves your post #5 concern.

[excel6000]

I actually moved my data to column C. So basically I use code to navigate to the cell where I want the formula, then I run the code which I put in Post #8 and it will sum up everything for that particular group of data. Then I used code again to navigate to the next cell where I need subtotals. What I ended up doing was navigating to the cell, then using the code from post #8, and then using a loop to run through it again until it gets to the bottom of the data.

[skywriter]

Well why aren't you sharing the code? This is a learning site. If you have code that works it's customary to share the solution for the benefit of those coming here with a similar need or just those that want to learn how to code.

[StuCram]

To excel6000;

At first you asked to get a formula to add up a set of numbers in column B and that got answered.

Now, you've changed the problem in the same thread to get formulas for each of several groups of numbers in column B; perhaps that should have been a separate thread. Just a thought for future postings and questions.

Anyway, here's a solution to do that.
- Whenever a group of numbers in column B is followed by a blank cell,
a formula is inserted in that blank cell to get total of the numbers.
- additional blank cells are ignored.
- The total is formatted as red bold text for easy reference.

Hope this helps. See the attached file Make formulas for subtotals.xlsmfor a sample set of data and a button to run the macro.

Please Login or Register  to view this content.

[excel6000]

StuCram, you are correct, I should have started a new thread. I apologize for that. Thank you very much for your help!

Skywriter, below is the rest of my code which you asked for. Please also see image below (or actual excel file which is attached) to see what I was trying to accomplish. Sorry for all of the confusion.

Please Login or Register  to view this content.

Capture.JPG

[skywriter]

I'm not sure why you didn't see how well the code I posted in post #7 would work for your needs.
Here's a modified version to fit the file you posted.
There's a working file below the code.

Please Login or Register  to view this content.