Calculations 1

[GoodB]

I have an equation with 4 variables. I would like to be able to input any amount of rows of data into the spreadsheet. There will be 4 columns and multiple rows. Each is calculated separatly and then the sum will be calculated at the end. This is integration...
Example:
Row1: a, b, c, d Calculation1 = (1/2)*(b-a)*(d+c)
Row2: e, f, g, h Calculation2 = (1/2)*(f-e)*(h+g)
Row3: i, j, k, l Calculation3 = (1/2)*(j-i)*(l+k)
Row: ...,...,... Calculation = (1/2)*etc...
Sum=Calculation1+Calculation2+Calculation3+...+Calculation

Commas indicate separation of columns.
It would also need to stop when it comes across the first empty row.
Do I need to use an array? If so, please demonstrate. I can't get my code to step through each row and store the calculation to sum at the end.

Thank you for you help! It is much appreciated!
GoodB

 

[macropod]

Hi GoodB,

What code are you using?

Cheers

[MS MVP - Word]

 

[GoodB]

Good Mornin' Macropod,
I'm using MS Visual Basic.

 

[AnotherHiggins]

If you're having trouble getting started with the code, turn on your macro recorder (Tools > Macro > Record new macro) and do the steps as far as you can. Post the generated code.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

Hi GoodB,

What code as in what is your code?

Cheers

[MS MVP - Word]

 

[GoodB]

That's the thing, I'm not really sure how to start the code. I have done code like this in the past but I can't remember how to do it, it's been a few years...
Here is what I recorded with the macro recorder.
Sub Calc()
Range("J24").Select
ActiveCell.FormulaR1C1 = _
"=(1/2)*(R[-21]C[3]-R[-21]C[2])*(R[-21]C[4]+R[-21]C[5])"
Range("J25").Select

End Sub

 

[AnotherHiggins]

All of the following assumes that your data is in columns A and you'll want to put your formulas into column E.

The first thing to do is figure out how many rows you have. There are multiple ways of doing this.

See faq707-2112 and faq707-2115 for more information on this. What I'll do is start from the bottom of Column A, then find the first populated cell searching up that column.

Then you just want to put your formula into column E from Row 2 (assuming that Row 1 is a header row) down to the last row. It will look something like this:

Sub Calc()
    intLstRow = Range("A" & Application.Rows.Count).End(xlUp).Row
    Range("E2:E" & intLstRow) = _
            "=0.5 * (RC[-3]-RC[-4]) * (RC[-1]+RC[-2])"
End Sub

And I'd suggest putting the sum at the top, not the bottom. As SkipVought has pointed out in this forum many times, placing totals at the bottom is a vestige left over from the days of paper, when you could flip to the last page and find summary data. Don't make the people looking at this on a screen scroll down looking for the totals, just bump the data down a few rows and place totals at the very top.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[electricpete]

If there is no more data below the data being used for the calculation (or you can re-arrange to eliminate anything below), then you don't even need vba code.

Just type the following formula in some cell (let's say E1) and press control-shift-enter (instead of enter) to make it an array formula.

=SUM(0.5*(B:B-A:A)*(C:C+D))

If there is something else below this, then the vba code posted 18 Jul 08 11:08 wont' work, because it is looking for the first empty row searching up from the bottom.

Certainly it is not a big challenge to overcome. But I would like to understand before we go any further if there is more data below this.

 

[GoodB]

Here is what I have so far...

Sub Calc()
Dim RowVar, ColVar, Sum, Integration As Double
Dim I As Integer
RowVar = 3
ColVar = 12
I = 0
Sum = 0
Range("L3").Select
Do
I = I + 1
Sum = Sum + Integration
Integration = (1 / 2) * (Cells(RowVar + I, ColVar + 1) - Cells(RowVar + I, ColVar)) * (Cells(RowVar + I, ColVar + 3) + Cells(RowVar + I, ColVar + 2))
Loop Until I = 40
End Sub

I don't want any code in the spreadsheet, I would like it all to be in the vb code.
The code above works somewhat but I can't figure out how to sum the individual integrations.

 

[electricpete]

Here is code that works to give the correct result.

Sub Calc()
Dim sum As Double, Integration As Double
Dim I As Integer
    
    Dim rowvar As Integer
    Dim colvar As Integer
        
    ' assume the data starts in third row and 12th column
    rowvar = 3
    colvar = 12
        I = 0
    
    sum = 0
    Do
       Integration = (1 / 2) * (Cells(rowvar + I, colvar + 1) - Cells(rowvar + I, colvar)) * (Cells(rowvar + I, colvar + 3) + Cells(rowvar + I, colvar + 2))
       Stop
    sum = sum + Integration
    I = I + 1
    Loop Until I = 40
    MsgBox "result is " & sum
End Sub

 

[electricpete]

I tried to make the code as close as possible to yours.
Note your dimension statement left RowVar, ColVar, Sum as variant which is probably what not what you intended.

Also it's not clear what is required as far as determining the range where the data lies. Could ask user to select range. Could use a named range. Could use a named range for start and continue until find a blank. Etc.

 

[electricpete]

I cleaned up the code a little more - deleted stop, fixed the indenting (smartindent), and broke up the very long formula onto multiple lines

Sub Calc()
Dim sum As Double, Integration As Double
Dim I As Integer

Dim rowvar As Integer
Dim colvar As Integer

    ' assume the data starts in third row and 12th column
    rowvar = 3
    colvar = 12
    
    ' Initialize the loop
    I = 0
    sum = 0
    
    Do
        Integration = (1 / 2) * (Cells(rowvar + I, colvar + 1) _
                                 - Cells(rowvar + I, colvar)) * (Cells(rowvar + I, colvar + 3) _
                                                                 + Cells(rowvar + I, colvar + 2))

        sum = sum + Integration
        I = I + 1
    Loop Until I = 40
    MsgBox "result is " & sum
End Sub

 

[AnotherHiggins]

electricpete:

A) Why in the world would there be other information under a data table, particularly one with a variable number of rows?

B) I linked to two different FAQs that offer other methods to calculate the last row

C) Looping is inefficient and should be avoided when possible. In this case it is far less efficient because all you need to do is put the formula in a predetermined number of rows (if you want to use a 'helper column').

D) I really like your array formula approach. That's a fine idea.

GoodB:
It doesn't seem that you want the individual rows summed in a 5th column, so I think electricpete's idea of an array formula is a great way to go. But you can't use references for entire columns in an array formulas.

Instead, try this:
[tab]=SUM(0.5 * (B2:B65536 - A2:A65536) * (C2:C65536 + D265536))

Or, if you want or need to use VBA, you can, again, turn on the macro recorder and capture the steps of entering the array formula. You could also use a calculated last row as outlined above.

It would look something like this:

Sub Calc_1()
    intLstRow = Range("A" & Application.Rows.Count).End(xlUp).Row
    Range("E2").FormulaArray = _
        "=SUM(0.5 * (B2:B" & intLstRow & " - A2:A" & intLstRow & ") * (C2:C" & intLstRow & " + D2:D" & intLstRow & "))"
    Range("E2").Value = Range("E2").Value
End Sub

Again, there are other methods to calculate intLstRow, but I suspect what I have will work for you. The last line of code replaces the formula with the number that it returns.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[GoodB]

electricpete,
Your solution worked for me, I had my I=I+1 switched! Frustrating!

Thanks for your help!

Thank you everybody for all your help, I appreciate it. I learned a lot from the discussions! I hope to return the favor someday.

 

[electricpete]

anotherhiggins

A - I was just keeping an open mind to the possibility there was data below the data in question. The statement "It would also need to stop when it comes across the first empty row." led me to believe this was a possibility.

C - That's how I checked the result of the vba was correct and how I would have done it myself. But the OP stated "I don't want any code in the spreadsheet, I would like it all to be in the vb code."


D - Thanks for catching the error in my array formula. I had initially checked it out using a slightly different form with arguments such as "A:A myrange" where myrange is named range of rows of interest and space is the intersection operator. Tried to make it simpler by taking out the myrange but forget to check the resulting formula.