New to excel and need help with automated subtraction

[Cainy]

Hi guys,

I recently just started using excel and this forum and wanted to know how to make a spreadsheet that has
automated subtraction in it. So when I enter a number in the subtraction box, it will automatically take away
from the whole number in another cell. Then disappear so I could then enter a different number in and press
enter and keep repeating the process well taking away from that one whole number. For example:

Total owed: 2000
Subtraction number: Entered number here to subtract from 2000 then the number disappears and can enter a different number to keep subtracting from number above.

Sorry if this is hard to understand. Would be great if someone could help...thank you.

I have also attached a image but am unsure if people can view it. http://i62.tinypic.com/2hr24wi.png

 

[John_w]

Put this code in the worksheet module of the sheet containing the relevant cells. For example if the cells are in Sheet1, right-click the Sheet1 tab and click View Code to open the VBA editor and paste the code in the right-hand pane.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("C2") Then
        Application.EnableEvents = False
        Range("A2").Value = Range("A2").Value - Range("C2").Value
        Range("C2").ClearContents
        Application.EnableEvents = True
    End If
End Sub

It assumes the total value is in cell A2 and the value to subtract is in C2.

 

[Cainy]

That worked great, thank you. I was just wondering if its possible to edit the code so it still works when you merge cells together? I want to merge cells together so I can make the numbers size bigger.

 

[Cainy]

And I also wanted to do exact copy on the same spreadsheet if possible? So for example:
Would it be possible to extend the ("A2") to something like ("A2")("B2")?

Range("A2").Value = Range("A2").Value - Range("C2").Value</pre>

 

[Michael M]

Avoid merged cells wherever possible....I'd suggest maybe an Autofit of rows and columns, as a better option.
Merged cells AND VBA will give you all sorts of problems later on !!!

 

[John_w]

And I also wanted to do exact copy on the same spreadsheet if possible? So for example:
Would it be possible to extend the ("A2") to something like ("A2")("B2")?

I don't quite understand what you mean, but if you want a copy of A2 in B2 then this code does that and also works with merged cells A2:A3, B2:B3, C2:C3.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target = Range("C2") Then
        Application.EnableEvents = False
        Range("A2").Value = Range("A2").Value - Range("C2").Value
        Range("B2").Value = Range("A2").Value
        Range("C2").Value = ""
        Application.EnableEvents = True
    End If
End Sub

 

[Cainy]

Got it working correctly with a few minor tweaks Thanks guys for your help.