Stumped on 2 things...

[mungojeerie]

Hi there,
I need help with two things. I want to add a button to a excel sheet that when clicked preforms simple calculations (-/+)

A dumbded down version of my data is as follows:

<table width="192" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64" span="3"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Item</td> <td class="xl24" style="width: 48pt;" width="64">[+/-]</td> <td class="xl24" style="width: 48pt;" width="64">Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Apple</td> <td class="xl24">2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Banana</td> <td class="xl24">-2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Orange</td> <td class="xl24">2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Pear</td> <td class="xl24">-2</td> <td class="xl24">5</td> </tr> </tbody></table>
The columns "Item" and "[+/-] will be on one worksheet and the Total column will be located on a different worksheet within the same workbook.

I want the button to when clicked add the value in [+/-] to the value in Total so that the the "Total" columns values would read then read as:

Item Total
Apple 7
Banana 3
Orange 7
Pear 3

and then at at later dates the values in the [+/-] column could be changed again but not effect the Total column until the button is pressed.

So... is it possible to do this addition/subtraction when one value of the formula is also the result cell?

And if so... how the heck do I do this??? haha

thank you in advance to taking the time to look at this.

Ryan

 

[VoG]

Maybe

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        .Value = .Value + .Offset(, 1).Value
        .Offset(, 1).ClearContents
    End With
Next i
End Sub

 

[mungojeerie]

Maybe

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("B" & i)
        .Value = .Value + .Offset(, 1).Value
        .Offset(, 1).ClearContents
    End With
Next i
End Sub

I appreciate you taking the time to post this for me, however Im not really sure how to impliment it and adjust it for my needs...

 

[VoG]

Which version of Excel are you using?

 

[mungojeerie]

which version of excel are you using?

2003

 

[VoG]

View > Toolbars > Control Toolbox. Click on the button icon then draw a button on the sheet. Right click the button, select View Code and replace whatever is in the code window with the code that I posted. Press ALT + Q to close the code window. On the Controls Toolbar click the Exit Design Mode button then try clicking the button.

 

[mungojeerie]

Okay Ive finally found a little time to sit and work on this.. so yes I got the code entered for the button and it does function

however I need to configure it a bit and dont exactly know how to do that.. i tried searching the web with strings from the formula to see if I could figure out what functions they serve but I havent had much luck.

I know nothing of VB and dont expect anyone to explain to me all the functions in a quick note on a forum, but if you could modify the code for me quickly to suit my needs i would be able to use it and be able to compare it to the original code to help understand what is performing what function.

Currently on my spreadsheet I have:

A                 B             C

<table width="192" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"><td class="xl24" style="height: 12.75pt; width: 48pt;" width="64" height="17">Item</td> <td class="xl24" style="width: 48pt;" width="64">[+/-]</td> <td class="xl24" style="width: 48pt;" width="64">Total</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Apple</td> <td class="xl24">2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Banana</td> <td class="xl24">-2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Orange</td> <td class="xl24">2</td> <td class="xl24">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">Pear</td> <td class="xl24">-2</td> <td class="xl24">5</td></tr></tbody></table>
The formula provided earlier does do the correct math functions upon click of the button, however columns B and C are reversed it adds or subtracts the numbers in column C and puts the total in col B. I need to reverse this so that the Total appears in the correct column.

Originally I wanted the total to appear on a different worksheet but I can work around that and do with the total just showing up in column C.

Now the items in Column A will be greatly expanded on... spanning a minimum of 60 rows... does the formula need to change at all in order to reflect that?

Thanks again for your time, it's very appreciated.

Ryan

 

[VoG]

The macro calculates the number of rows (LR).

 

[mungojeerie]

Great! Thanks VoG, now what about switching the columns around so that the add/subtract number is listed in column B and the sum of the two ends up in column C?

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    With Range("C" & i)
        .Value = .Value + .Offset(, -1).Value
        .Offset(, -1).ClearContents
    End With
Next i
End Sub