Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Andrzejek on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

(Excel) A1 = A1+B1 1

Status
Not open for further replies.
WingandaPrayer

WingandaPrayer

Technical User
May 15, 2001
152
Afternoon,

I quick question please.

As in the heading, how do I create the following using auto calc rather than manual (F9)

Cell A1 = a1+b1

add 5 to b1 and a1 becomes 5
change b1 to 7 and a1 becomes 12

Cheers
 
Sort by date Sort by votes
Not a great idea to do this, but if you must, then
go to Tools, Options, Calculation.
set Iteration to 1
in cell a1 type =a1+b1
 
Upvote 0 Downvote
Or

Right click on th esheet tab. select View code and then place the following code in the resulatant window.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1), [B1]) Is Nothing Then
        [a1] = [a1] + [B1]
    End If
End Sub
A.C.
 
Upvote 0 Downvote
Thanks for the replies

onedtent ~
thats how I currently work it using F9 although I have to add all entries then manual calc otherwises auto calc updates each row everytime I change rows.
[smile]


acron

Could the code be expanded to work for more than one row.

At present I am probably testing with 30 rows columns A and B.

Using F9 to calculate I enter all rows then I can either overwrite or delete info in Column B [smile]

Thanks
 
Upvote 0 Downvote
Try the following which works for 30 rows (change B1:B30 to reflect th erows you want to work with).
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target.Cells(1), [B1:B30]) Is Nothing Then
       Target.Cells(1).Offset(0, -1) = _
            Target.Cells(1).Offset(0, -1) + _
                Target.Cells(1)
    End If
End Sub
BTW, the code only works for one cell at a time, so that if you paste a series of values into B1:B30, only the first cell in A will be updated. I'm sure the code could be amended if need be to allow multiple cell updates at once.

A.C.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

zinderellie
  • Locked
  • Question
Excel 365- Formula Help - When A1=X and B1=Y, Return C1
Replies
8
Views
243
dgillz
dgillz
renigar
  • Question
Having trouble with Excel Lookup
Replies
4
Views
355
renigar
renigar
Bijugate
  • Locked
  • Question
Excel update question
Replies
0
Views
386
Bijugate
Bijugate
Pirouette
  • Locked
  • Question
Excel workbook
Replies
1
Views
379
combo
combo
shytott
  • Locked
  • Question
Excel - Toggle Button Code 1
Replies
10
Views
1K
shytott
shytott

Part and Inventory Search

Sponsor

Back
Top