Excel Formula & Value in same Cell

July 25, 2010 at 20:46:28
Specs: Windows 7
I want to add the number in Cell B1 to the number in Cell A1, But have the result in Cell A1
i.e. A1=A1+B1

See More: Excel Formula & Value in same Cell

Report •


#1
July 26, 2010 at 04:32:35
Hi,

You can't do it with standard Excel formulas.

You can do it with Visual Basic code.

Right-click the name Tab of your worksheet and select View code.

In the Visual Basic window that opens enter this:

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
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub

From the Visual Basic menu bar click File- Save
Click Alt+f11 (the Alt key and function key #11 clicked together) to return to the main Excel window.

Now when the value in cell B1 is changed, the value in cell A1 will be updated to its original value plus the new value in cell B1.

This is triggered by Excel's 'on change' event. The code then tests that the changed cell was cell B1 and if it was it does the required addition.

Regards


Report •

#2
July 26, 2010 at 11:55:49
Hi,
Many thanks for that, it does exactly what I wanted, and was simple to set up.

Kindest regards


Report •

#3
July 26, 2010 at 12:06:56
Hi,

You're welcome.

Thanks for the feedback

Regards

Humar


Report •

Related Solutions

#4
January 5, 2011 at 12:16:31
This was great help Thank you Humar for your help and knowledge.

I would like to ask if there is a way to use the same code not for one cell but to be used for 100 cells. I spend hours trying to modify it to work for A1:A100 and B1:B100, but without success, unless I redo it 100 times. Example for 2 cells, but I need for 100 without retyping or copy pasteing and editing it 100 times:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$1" Then
'disable events
Application.EnableEvents = False
'add E1 value to D1 and save in D1
Range("D1").Value = Range("D1").Value _
+ Range("E1").Value
End If
If Target.Address = "$E$2" Then
'disable events
Application.EnableEvents = False
'add E2 value to D2 and save in D2
Range("D2").Value = Range("D2").Value _
+ Range("E2").Value
End If
'reenable events
Application.EnableEvents = True
Exit Sub
'error handler
ErrHnd:
Err.Clear
'reenable events
Application.EnableEvents = True
End Sub

!!!!PLease if you can help I would highly appreciate - please if possible email reply to xxx@xxxx.xxx attn:Mikayel

edited by moderator: email address deleted


Report •

#5
January 5, 2011 at 13:40:04
I haven't really followed this thread, but this modification will add the value entered anywhere in E1:E100 to the value in Column D of the same row:

If that's not what you are looking for, let me know some more specifics. You used A1:A100 and B1:B100 in your text, but you used E1 and E2 in your code so I'm not sure what range you are really working with.

Private Sub Worksheet_Change(ByVal Target As Range)
'Was change made within E1:E100?
  If Not Intersect(Range(Target.Address), Range("E1:E100")) Is Nothing Then
'disable events
     Application.EnableEvents = False
'add E(row) value to D(row) and save in D(row)
       Range("D" & Target.Row).Value = Range("D" & Target.Row).Value _
         + Range("E" & Target.Row).Value
   End If
    Application.EnableEvents = True
 Exit Sub
'error handler
ErrHnd:
 Err.Clear
'reenable events
 Application.EnableEvents = True
End Sub

P.S. If you need to post any more code or data, please read the How To referenced in my signature line before hand.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question