Help with a loop function in VBA

S

stinger85

New Member
Joined
Jul 15, 2011
Messages
7
The equation below would be in column c

At any row = n

Cn = (Bn - B(n-1))*(An - A(n-1)) + (B(n-1) - B(n-2))*(An - A(n-2)) + (B(n-2) - B(n-3))*(An - A(n-3)) . . . . .etc

Columns A and B will contain given numbers. Column a will range from 0 to X in increments of 1, column B will start at 0 and contain increasing values from there. Column C will perform a calculation as shown above in A & B and will sum equations with rows above.

This equation would get longer as the rows increased, so I would like to be able to have a vba code with some sort of loop to do the summation for me.

Let me know if I need to clarify anything.

Here is an example:

Below shows columns A, B and C and then after the "=" is shown the equation in column C

<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { }</style> <table style="border-collapse: collapse;" border="0" cellpadding="0" cellspacing="0" width="548"> <col style="" width="34"> <col style="" width="29"> <col style="" width="35"> <col span="6" width="75"> <tbody><tr height="13"> <td class="xl24" align="right" height="13" width="34">0</td> <td class="xl24" align="right" width="29">0</td> <td class="xl24" width="35">
</td> <td class="xl24" width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">1</td> <td class="xl24" align="right">1</td> <td class="xl24" align="right">1</td> <td class="xl24">=((B2-B1)*(A2-A1))</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">2</td> <td class="xl24" align="right">3</td> <td class="xl24" align="right">4</td> <td class="xl24">=((B3-B2)*(A3-A2))+((B2-B1)*(A3-A1))</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">3</td> <td class="xl24" align="right">5</td> <td class="xl24" align="right">9</td> <td class="xl24">=((B4-B3)*(A4-A3))+((B3-B2)*(A4-A2))+((B2-B1)*(A4-A1))</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">4</td> <td class="xl24" align="right">7</td> <td class="xl24" align="right">16</td> <td class="xl24">=((B5-B4)*(A5-A4))+((B4-B3)*(A5-A3))+((B3-B2)*(A5-A2))+((B2-B1)*(A5-A1))</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: windowtext; font-size: 10pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Verdana; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl24 { }</style> <table style="border-collapse: collapse; width: 548px; height: 90px;" border="0" cellpadding="0" cellspacing="0"> <col style="" width="34"> <col style="" width="29"> <col style="" width="35"> <col span="6" width="75"> <tbody><tr height="13"> <td class="xl24" align="right" height="13" width="34">
</td> <td class="xl24" align="right" width="29">
</td> <td class="xl24" width="35">
</td> <td class="xl24" width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> <td width="75">
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">
</td> <td class="xl24" align="right">
</td> <td class="xl24" align="right">
</td> <td class="xl24">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">
</td> <td class="xl24" align="right">
</td> <td class="xl24" align="right">
</td> <td class="xl24">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">
</td> <td class="xl24" align="right">
</td> <td class="xl24" align="right">
</td> <td class="xl24">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> <tr height="13"> <td class="xl24" align="right" height="13">
</td> <td class="xl24" align="right">
</td> <td class="xl24" align="right">
</td> <td class="xl24">
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> </tr> </tbody></table>
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">16</td></tr></tbody></table><br /><br />

Code: 
Sub Eq_AnBn()
    
    Dim c() As Variant, v As Variant
    Dim i As Long, j As Long
    
    v = Range("A1", Range("B" & Rows.Count).End(xlUp))
    
    ReDim c(1 To UBound(v, 1), 1 To 1)
    For i = 2 To UBound(v, 1)
        For j = i To 2 Step -1
            c(i, 1) = c(i, 1) + ((v(j, 2) - v(j - 1, 2)) * (v(i, 1) - v(j - 1, 1)))
        Next j
    Next i
    Range("C1").Resize(UBound(v, 1)) = c
    
End Sub
 
Upvote 0
Welcome to the MrExcel board!

Cross-posting (posting the same question in more than one forum) is not against our rules. However, for the future, you might note point 24 of our Guidelines for Posting.

You might understand that some helpers may become disgruntled if they put time into developing a solution here only to find later that a solution had already been achieved elsewhere. They may then be less likely to help you next time.

If you do cross-post in the future but also provide a link, then there shouldn’t be a problem.

Cross posted at: http://www.excelforum.com/excel-programming/784449-need-help-summation-of-above-rows-using-a-loop.html
 
Upvote 0
Peter,

I apologize for the cross-posting, I can see why that would be a problem. Thank you for posting the link to the other forum for me.
 
Upvote 0
I have a follow up question if someone can help. Can the above macro be written as a function, so if the values in column A or B are changed the equation in column C would update just as a regular excel equation would. Thank you again for the help.

This question hasn't been posted anywhere else :)
 
Upvote 0
stinger85 said:
Column a will range from 0 to X in increments of 1, ..
Click to expand...
Perhaps you didn't really mean that, but if you did, couldn't we just use this simple worksheet formula to do the same job?

Excel Workbook
ABC
100
2111
3234
4359
54716
stinger85
 
Upvote 0
Thanks for the response Peter. The numbers shown are just a simple example to show the functionality I needed. In reality the calculation performed in column C will be more complicated and involve more numbers. I just wanted to simplify things to post up the question and I should be able to modify from there once I know the basic code to do what I need.

The code that AlphaFrog provided is what I was looking for, except that it just writes the answer in column C, if possible I need it to write an equation in column C so that it will update if numbers are changed in columns A or B without running the macro again.

Thanks!
 
Upvote 0
This event macro will run the equation macro automatically when you make a change to any cell in columns A or B

  • Right-click on the sheet tab
  • Select View Code from the pop-up menu
  • Paste the code below in the VBA edit window


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A:B"), Target) Is Nothing Then Call [COLOR="Red"]Eq_AnBn[/COLOR]
End Sub

Eq_AnBn is the name of the macro.
 
Upvote 0
Thanks, AlphaFrog, that worked when changing values manually. Is there a way to get this to work when using goalseek? The values in Column B will be changing with goalseek and Column C values are included in the equation that goalseek is iterating on. I need column C to update as the goalseek function is running. Sorry, guess I should have stated that before.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
168
dilshod_k
D
P
  • Solved
IF Functions
Replies
5
Views
194
Pippy79
P
D
  • Question
Formula help
Replies
2
Views
236
Fluff
Fluff
C
  • Solved
Index match function
Replies
3
Views
468
Alex Blakenburg
A
E
  • Solved
FILTER function to return ONLY records with most recent date
Replies
5
Views
423
Fluff
Fluff

Forum statistics

Threads
1,215,335
Messages
6,124,327
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top