CODEWITHSUNDEEP
excelvba
MikeS159
MikeS159

Reputation: 1974

Excel cell value update macro

I have a spread sheet for tracking different savings, so column A has a name, B has a currency value. I want to be able to enter a value in column C and have it update the B cell next to it, then return to 0. For example:

B1 = £50.00

I type -£12.00 in C1

B1 = £38.00

C1 = £0.00

I thought there would be a built in function, but I can't find one. I think I will need to write a macro to do this. Can anyone show me how this would be done?

Upvotes: 0

Views: 1244

Answers (2)

Tyger Guzman
Tyger Guzman

Reputation: 758

You'll want to add the following code to the sheet your working on. Right Click the sheet name Tab and choose View code and paste this in.

Private Sub Worksheet_Change (ByVal Target as Range)

Dim FirstNum as Currency 'Long is for number currency should help keep format 
Dim SecNum as Currency 

If Target.Column = 3 Then  'Only Runs if Cell being changed is in column C, Might need to be Columns 
    Application.EnableEvents = False ' Stop macro changes calling function repeatedly
    FirstNum = Target.offset(-1,0).Value ' Value in Column B 
    SecNum = Target.Value   ' Value being typed in C  
Target.Offset(-1,0).Value =  FirstNum - SecNum ' Makes Cell B equal to difference of previous value and value typed in C 
     MsgBox("Difference Found") ' Just to display code worked Remove when confirm code works
    Target.Clear ' Clears Value you typed 
    Application.EnableEvents = True ' Re-enable the macro call
End if 

End Sub

Upvotes: 1

Frantech
Frantech

Reputation: 1

Seems like you are wanting to do something here that is a little different to the way that excel would normally work. Suggest you use more rows then you wouldn't need a macro. e.g. B1 = $50 C1=-$12 then B2==IF(C1<>"",B1+C1,""). Then you copy cell B1 down to propegate the formulae in cells below. You enter you next value in C2. Does this do what you need?

Upvotes: 0

Related Questions