Reset figure in a cell to 1

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Is there a way to reset a figure in a cell back to "1" when I change a figure
in another cell.
i.e.
Cell "A1" = 57315
Cell "A2" = 8
When I change "A1" to 57316, then cell "A2" changes automaticly to 1.
 
Bob,

This can be done if the value in A2 is returned from a formula like below..

IF(A1=somevalue,if true return a value, if false return a value)
Formula in A2 =IF(A1=57315,8,1)

I am sure you are not looking at any particular number. If you can explain a
bit more about your requirement someone here should be able to help you...

If this post helps click Yes
 
Thanks Jacob,
The figure in "A1" can be any value, and in "A2" it's value = 1 to 10.
I'm after if the value in "A1" changes at all (to any value), I need the
value in "A2" to change back to the value of "1". In other words resetting
itself to "1".
I hope that makes sense!!
 
Install the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set a1 = Range("A1")
Set a2 = Range("A2")
If Intersect(t, a1) Is Nothing Then Exit Sub
Application.EnableEvents = False
a2.Value = 1
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Bob, you will require a macro to do this. If you are new to macros set the
Security level to low/medium in (Tools|Macro|Security). Right click the sheet
tab and view code. Paste the below code and try out

The value in A2 will be reset to 1; everytime when the value in A1 changes.
Try and feedback

Dim varTemp As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
If varTemp <> Target.Value Then
Range("A2") = 1: varTemp = Target
End If
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
 
Gary and Jacob,
I tried both but nothing happens. Any ideas
I followed the instructions to the letter.
 
Back
Top