Addition overlapping, macro needed?

Microsoft Office excel 2007 home & stude...
July 27, 2010 at 07:54:43
Specs: Windows 7
Hi, I'm a novice when it comes to Excel compared to most of the people here, and I did some searching for the subject, but I didn't know how to word it.

Anyways, I would like to be able to add up the following cells in this way.

A1 value + input A2 value = Change in A1 value
I would also prefer the A2 value to disappear after I press enter, if this is possible.

Same thing with subtraction
A1 value - A2 value = Change in A1 value

Is this possible? Any response is of great help, even one saying it's not possible. That way I can find another way to do it. :P

Thanks in advance!


See More: Addition overlapping, macro needed?

Report •


#1
July 27, 2010 at 08:33:14
You would need a Macro similar to the one posted by Humar in this thread:

http://www.computing.net/answers/of...

If you think that might work for you, let us know and we'll see what modifications are needed to fit your exact situation.


Report •

#2
July 27, 2010 at 08:43:11
That works great, thank you! However, there is no way of removing the input automatically? The number I'm adding to A1, that is. Not really needed, but it would be useful for me.

Also, how would I go about applying this to multiple cells? I've never used visual basic before, and I don't know where to start/stop copying the code before applying it to the other cells in my spreadsheet.

Copying the entire code twice gives me a compile error, so clearly I don't know what I'm doing here.


Report •

#3
July 27, 2010 at 08:59:18
You can't have more than one Worksheet_Change macro per sheet, but you can have the one allowed determine where the change was made and take different action based on the Target.Address. We'd need a little more info before we could suggest the proper syntax.

In the code below, I've added the required line to clear B1 after the value is entered.

You also mentioned "subtraction". If you expect the code to decide which operation to use, you'll need a way to pass that information to the code. Obviously the easiest way is to input a negative number when you want to subtract the value. There may be other options once we know the details of your requirements.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$1" Then
    'disable events
   Application.EnableEvents = False
    'add B1 value to A1 and save in A1
    Range("A1").Value = Range("A1").Value _
        + Range("B1").Value
    'clear B1
    Range("B1").ClearContents
  End If
'reenable events
 Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
 Err.Clear
'reenable events
 Application.EnableEvents = True
End Sub


Report •

Related Solutions

#4
July 27, 2010 at 09:11:31
Great, that clear cell thing was good!
Anyways, to just make clear what I try to make. Great help so far, I might add.

I'm employed at a software reseller company (newly started, and fairly small), and I'm trying to make a spreadsheet which assesses how much of each software we have in stock. I was planning to set it up like this:

"Software #1: In stock | New supply | Sold"
Where the "In stock" is our "a1", "New supply" is "a2" and "Sold" is a3.
My plan was to easily type in arrived software in a2, to add it up to the current value in a1. As well as type in amount of software sold in a3, to subtract from the current value in a1.

This would have to be done for many different software types, as we carry a bunch.

So, I would try to achieve:

A1 + A2 = Updated A1.
A1 - A3 = Updated A1
B1 + B2 = Updated B1
B1 - B3 = Updated B1
I reckon if you can do it with more than one, you can pretty much do it infinitely, or how long I can be bothered punching in numbers.

Hopefully that cleared it up, and hopefully it can be done without someone writing an essay of code. :)

Edit: Also, for simplicity's sake, I can write a negative number in the addition box, if that would make it a lot simpler.

Thanks!


Report •

#5
July 27, 2010 at 09:38:35
This version will add values entered in Row 2 and subtract values entered in Row 3 from the value in Row 1.

One question: Where will you put the names of your products?

Since the code is working on specific ranges/rows, you can't just insert Rows and Columns and expect the code to continue to work.

That's one of the drawbacks of macros: They are written to either work on specific, hard-coded ranges or need to be told how to determine which ranges to work on. Any changes to the worksheet layout need to be made while keeping the macro in mind.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Rows(2)) Is Nothing Then
    'disable events
   Application.EnableEvents = False
    'add B1 value to A1 and save in A1
    Target.Offset(-1, 0).Value = Target.Offset(-1, 0).Value _
        + Target.Value
    'clear B1
    Target.ClearContents
  End If
    If Not Intersect(Target, Rows(3)) Is Nothing Then
    'disable events
   Application.EnableEvents = False
    'add B1 value to A1 and save in A1
    Target.Offset(-2, 0).Value = Target.Offset(-2, 0).Value _
        - Target.Value
    'clear B1
    Target.ClearContents
  End If
'reenable events
 Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
 Err.Clear
'reenable events
 Application.EnableEvents = True
End Sub



Report •

#6
July 27, 2010 at 09:45:10
Well, the actual name of the product in my spreadsheet is at C2, the amount in stock is at D2, adding is E2, subtracting is F2.

I'll try that macro when I get back to work, and I'll probably post tomorrow whether I can figure it all out by myself or whether I need some more help. :)

Thanks a lot, at least!


Report •


Ask Question