PDA

View Full Version : VBA Help Needed with Dynamic Sum Formula



APish
04-04-2014, 10:55 AM
I need help in VBA with a formula. Been looking/trying for 8 hours but I'm too new at coding/dumb to figure it out.
I have the following table that follows a pattern. The pattern always starts with "Cat" and ends with "Lion" but there may be a different number of animals that are sandwiched in between "Cat" and "Lion" depending on the input file I am using for this VBA macro. Within each input file, the number of animals between "Cat" and "Lion" does not change. Refer to Column A for the two tables below.

I need a VBA code for new column "D" that adds up the prices within each pattern set (i.e. Col B + Col C). The VBA code needs to be flexible enough to calculate Total Prices for any kind of input file where Col A can have any number of animals sandwiched between "Cat" and "Lion". Again, within each input file, the number of animals sandwiched between "Cat" and "Lion" does not change. Please see tables below.

My grand idea was to create some kind of internal VBA constant that is defined by the number of rows from "Cat" to "Lion" (since the pattern always starts and ends with this) and uses this constant as an offset value. Using this constant, I would have a dynamic formula in D1 that is Sum(B1:B3,C1:C3), then moves the active cell to D4 by the offset value, adds the offset value to the row values in the formula so that it reads Sum(B4:B6,C4:C6) and so on, until the end of the file.

The table 2 example is the same idea, but the offset would be different. So long as we're defining the offset as number of rows from "Cat" to "Lion", it shouldn't matter.

I am too slow and stupid to understand VBA language but I have a gist of the logic. Can someone help me with the coding for this? Thanks a ton!!!




Table 1






A (Animal)
B (Price1)
C (Price2)
D (Total Prices)


1
Cat
1

3


2
Dog

1



3
Lion

1



4
Cat
2

6


5
Dog

2



6
Lion

1








Alternate Table






A(Animal Pattern 2)
B (Price1)
C (Price2)
D (Total Prices)


1
Cat
1

6


2
Dog

2



3
Horse

2



4
Lion

1



5
Cat
2

11


6
Dog

2



7
Horse

3



8
Lion

4

Simon Lloyd
04-04-2014, 12:53 PM
Where did you get the values 6 and 11 from?, it would help to know how you want to calculate (and to be honest you could use sumproduct)

APish
04-04-2014, 01:01 PM
Where did you get the values 6 and 11 from?, it would help to know how you want to calculate (and to be honest you could use sumproduct)

Value 6 is the sum of B1:B4,C1:C4.

Value 11 is the sum of B5:B8,C5:C8

I need to calculate the sum of values in columns B+C within each set of rows from "Cat" to "Lion".

Simon Lloyd
04-04-2014, 01:17 PM
You're very unclear!
Your first table doesnt go with the calculation to get 6, are you really counting animals? it would be better if you can give a bit of real world data in a sample workbook, showing the structure you would have before hand and what you'd expect to be able to see and why.

APish
04-04-2014, 01:42 PM
You're very unclear!
Your first table doesnt go with the calculation to get 6, are you really counting animals? it would be better if you can give a bit of real world data in a sample workbook, showing the structure you would have before hand and what you'd expect to be able to see and why.


Table 1 should read "5" in cell D4. I didn't know how to edit the post. Sorry for the confusion.


Yes, I am counting animals. The sample workbook would be the same as what I posted, sans cell D4 typo.

I will attach one. 11510

APish
04-04-2014, 03:43 PM
Anybody?

This is causing great psychological stress. Any help would be appreciated. I'll clarify as much as I can or provide alternate files.

Bob Phillips
04-04-2014, 03:51 PM
I Think I see why the Cats get those numbers, but in the second table, why doesn't the first Dog have a D total of 7, Horse have 7, and Lion 8? Why just Cat?

APish
04-04-2014, 04:04 PM
The sample file I included should make it a little more clear. This is really making me hate life. D1 is sum(B1:B3,C1:C3). D4 is sum(B4:B6,C4:C6). Yes, I made a typo in Table 1. D4 should be "5".

Sorry.

Simon Lloyd
04-04-2014, 04:17 PM
The sample file I included should make it a little more clear. This is really making me hate life. D1 is sum(B1:B3,C1:C3). D4 is sum(B4:B6,C4:C6). Yes, I made a typo in Table 1. D4 should be "5".

Sorry.xld is asking why you're not interested in capturing the totals of dogs, horses...etc, this is why i said you're not really counting animals are you, if ALL you want to do is sum all the totals between a start and end point then there's no need for the cloak and dagger in between, for a formula based answer it really does matter what's in column A as to whetehr it will be text, numbers, dates or a mixture of any of those.

This is the only reason we're sking you for more info and clarification, if it's making you hate life then may i respectfully suggest you give the project to someone else, it's really not worth getting stressed over :)

APish
04-04-2014, 04:40 PM
Column A format is Text. I need a VBA code to sum the values between a Start and End point defined Column A. The start and end points can be called "Start" and "End", "Cats" and "Tigers" or "In-Situ Adenoma" and "Hyperplastic Polyp." It shouldn't make a difference?

The point I was trying to make was that there can be different N rows between the start and end point, which will affect the Column D sum formula.

I was wondering if all of you very smart people could help with VBA to define the Integer Range from "Start" to "End" using some kind of function/subroutine, and then use this Integer in a dynamic R1C1 "Sum" formula. Or maybe this isn't even the best way to go about it. I don't know.

Online I found something that is function-based:

Function countdistance(rng as range) as integer
For i=1 to i=100 step 1
if Rng.offset(i,0).value="Tiger" then
countdistance=i
exit function
end if
next
end function

Is this what could be used to define the distance between "Start" and "End"? How do I use the "i" that is defined through a function in a dynamic Sum formula?

I am really horrible with VBA. There is nobody else I can hand this project off to. Thanks for your time. Sorry for not being experienced enough to figure this out myself.

APish
04-04-2014, 05:01 PM
Never mind. There is a formula that can accomplish the task that I can put into VBA: =IF(A3<>"cat", "", SUM(OFFSET(B3, , , MATCH("Lion", A3:A10000, 0), 2)))

Thanks for the discussion all. Sorry for being confusing.

Simon Lloyd
04-04-2014, 05:26 PM
So you really wanted a formula???? how will that be dynamic if you are bringing data in to a sheet? so you bring in data that has 8 rows between each cat and lion, will you then place your formula manually for each section? will you amend each formulas' reference for the right section manually?

APish
04-05-2014, 09:36 AM
So you really wanted a formula???? how will that be dynamic if you are bringing data in to a sheet? so you bring in data that has 8 rows between each cat and lion, will you then place your formula manually for each section? will you amend each formulas' reference for the right section manually?

Well, ideally no, the solution would not be to program VBA to copy/paste a general formula directly into cells. But I was apparently too unclear in my description or people did not feel like helping because I used animal names in Column A (or some combination of both).

I can make the formula "dynamic" in the sense that I can use VBA to fill the formula from the top row to the last row with data.



CurRow = 2
MaxRow = Range("A" & Rows.Count).End(xlUp).Row
'Insert Formula Here
Range("D2").AutoFill Destination:=Range("D" & CurRow, "D" & MaxRow)

Like I wrote in the lengthy description to the post, I was looking for some means to determine the distance between 2 items in my list, use that distance in some kind of R1C1 formula to sum up totals in 2 columns.

I really struggle with the syntax and language of VBA because I don't have programming experience, so I was looking for some friendly help or pointers. In lieu of that, and in lieu of the type of VBA code I originally planned I'll just use the formula a friendly person offered on another message board.

Thanks for your time.

Simon Lloyd
04-05-2014, 02:51 PM
Well, firstly you're right to go with a formula solution any time you can as it's quicker than vba (inbuilt functions always are). We always like to work with real world data as we could work on a solution given that someone says "there's always text in the cell" only to find its a date so the formula wont work, you then get a lot of to and fro to try and get to the base of what they need. If you're happy to go ahead with what you have then great, if you want me to work on something to determine the distance and enter the sum formula for you then i will.

APish
04-06-2014, 08:18 PM
Well, firstly you're right to go with a formula solution any time you can as it's quicker than vba (inbuilt functions always are). We always like to work with real world data as we could work on a solution given that someone says "there's always text in the cell" only to find its a date so the formula wont work, you then get a lot of to and fro to try and get to the base of what they need. If you're happy to go ahead with what you have then great, if you want me to work on something to determine the distance and enter the sum formula for you then i will.

You are too kind to offer me, a complete stranger, this help in creating such a formula. I really appreciate it. I am going to see if the formula solution will suffice for now but if it falls apart due to some unforeseen issue, I will definitely be back. Thank you again for your time.