Help with a loop function in VBA

[stinger85]

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>

 

[AlphaFrog]

How many rows max would you have to calculate for?

Also, whatever you're doing to format your replies all on one long line makes it difficult to read.

 

[AlphaFrog]

<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;;">0</td><td style="text-align: right;;">0</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3</td><td style="text-align: right;;">5</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4</td><td style="text-align: right;;">7</td><td style="text-align: right;;">16</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=FXANBN(<font color="Blue">$A$1:B2</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=FXANBN(<font color="Blue">$A$1:B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=FXANBN(<font color="Blue">$A$1:B4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=FXANBN(<font color="Blue">$A$1:B5</font>)</td></tr></tbody></table></td></tr></table><br />

Function FxAnBn(rngV As Range)
    
    Dim c As Variant, v As Variant
    Dim i As Long, j As Long
    
    v = rngV
    i = UBound(v, 1)
    
    For j = i To 2 Step -1
        c = c + ((v(j, 2) - v(j - 1, 2)) * (v(i, 1) - v(j - 1, 1)))
    Next j
    
    FxAnBn = c
    
End Function

NOTE: Remember to remove the Private Sub Worksheet_Change event macro.

 

[stinger85]

This would be for about 100 rows max. That last code does exactly what
I was looking for.

Once again, thank you so much for taking the time to help me! I think I
now have everything I need.