How to retain last number in a cell

[Sandeep Himatsingka]

In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in
R1C3.
In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite
57 with 807 & so on. R1C2 is being filled regularly manually by me with
different numbers.
I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I
delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number
(which it should have been copying thr' an excel formula) from R1C2 & STOP
further copying same number from R1C2 unless R1C3=1.

How to do it? Pls give the formula in R1C1????
Many thanks

 

[JLatham]

I don't think you can do this with VBA code. Any formula that 'remembers'
R1C2 will not remember it once it changes.

This code will do what you want. Select the sheet and right-click on its
name tab and choose [View Code] from the list and then copy and paste this
code into the code module presented to you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:C1")) Is Nothing Then
Exit Sub
End If
If Range("C1") = 1 Then
Range("A1") = Range("B1")
End If
End Sub

 

[Sandeep Himatsingka]

Thanks LLatham for yr prompt help.
But I do not know How to do ' from the list and then copy and paste this

code into the code module presented to you.' Pls help immdtly & also reply whether my problem can be solved thr' this??

Is there no formula in excel for R1C1??

WAITING!!!!!

I don't think you can do this with VBA code. Any formula that 'remembers'
R1C2 will not remember it once it changes.

This code will do what you want. Select the sheet and right-click on its
name tab and choose [View Code] from the list and then copy and paste this
code into the code module presented to you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:C1")) Is Nothing Then
Exit Sub
End If
If Range("C1") = 1 Then
Range("A1") = Range("B1")
End If
End Sub

In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in
R1C3.
In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite
57 with 807 & so on. R1C2 is being filled regularly manually by me with
different numbers.
I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I
delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number
(which it should have been copying thr' an excel formula) from R1C2 & STOP
further copying same number from R1C2 unless R1C3=1.

How to do it? Pls give the formula in R1C1????
Many thanks

 

[JLatham]

There is no worksheet formula for R1C1 that I know of that will do this.

This page, on my site, will help you to get the code from the post above
into your workbook:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

If you are still unable to get it to work for you, send an email to me
(remove spaces from this address)
Help From @ jlathamsite.com
with your workbook attached and I will put the code into it and return it to
you.

Thanks LLatham for yr prompt help.
But I do not know How to do ' from the list and then copy and paste this

code into the code module presented to you.' Pls help immdtly & also reply whether my problem can be solved thr' this??

Is there no formula in excel for R1C1??

WAITING!!!!!

I don't think you can do this with VBA code. Any formula that 'remembers'
R1C2 will not remember it once it changes.

This code will do what you want. Select the sheet and right-click on its
name tab and choose [View Code] from the list and then copy and paste this
code into the code module presented to you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:C1")) Is Nothing Then
Exit Sub
End If
If Range("C1") = 1 Then
Range("A1") = Range("B1")
End If
End Sub

In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2 actions in
R1C3.
In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then overwrite
57 with 807 & so on. R1C2 is being filled regularly manually by me with
different numbers.
I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The moment I
delete 1 from R1C3 (make it blank), I want R1C1 should retain the last number
(which it should have been copying thr' an excel formula) from R1C2 & STOP
further copying same number from R1C2 unless R1C3=1.

How to do it? Pls give the formula in R1C1????
Many thanks

 

[MyVeryOwnSelf]

In R1C3, I fill manually 1 or delete this 1 (i.e. blank). Only 2

actions in R1C3.
In R1C2, I fill,say, number 30 then overwrite 30 with say 57 then
overwrite 57 with 807 & so on. R1C2 is being filled regularly
manually by me with different numbers.
I want in R1C1 same number of R1C2 as long as R1C3 contains 1. The
moment I delete 1 from R1C3 (make it blank), I want R1C1 should retain
the last number (which it should have been copying thr' an excel
formula) from R1C2 & STOP further copying same number from R1C2 unless
R1C3=1.

How to do it? Pls give the formula in R1C1????
Many thanks

This might work.

First in
Tools > Options > Calculation
check the "Iteration" box to allow circular references.

Then in R1C1 put
=IF(R1C3=1,R1C2,R1C1)

 

[JLatham]

The danger with allowing circular reference calculations is that they can
hide one that you set up later that you really didn't mean to.
The OP did send me a single-sheet workbook and I put the code I provided
earlier into it along with including that code in a .txt file and giving what
I hope are sufficiently detailed instructions on how to get it into his
real-world workbook so that it can be done 'cleaner' than resorting to an
intentional circular reference solution.
But I suppose that if he continues to struggle with inserting the code into
his workbook, that this is one possible alternative.

 

[JLatham]

Sandeep,
Please send new email with attachments as recently sent - system with your
email address and those files crashed and I do not have access to them now.
These are the 3 messages you sent on 9/9/2008
Thank You
JLatham (HelpFrom @ Jlathamsite.com)