Hello group such good day.
Do I make myself clear:
A1 I have a cell that can enter values manually, for example numbers: 1, 2, 3, etc.
I have the cell B1 has a value of cell A1.
ie if A1 = 2, B1 B1 = 2 should appear.
Now
if I modify: A1 = 3, cell B1 MUST REMAIN 2 (B1 = 2).
and in cell B2 it says the new value A1 (B2 = 3) should appear.

if not understood, there is a way to do with formulas ?, or if any macros,
if I could give an idea of a macro, because I'm not very familiar with macros theme.

thanks for the reply you can give me a greeting.

hi luis vejarano,

voir la solution de MichD sur microsoft.public.fr.excel
see MichD solution on microsoft.public.fr.excel
ver solución MichD en microsoft.public.fr.excel
vedi soluzione MichD su microsoft.public.fr.excel
I'm sorry if I did not recognize your language, please tell me.

isabelle

Le 2015-10-06 12:05, luis vejarano a écrit :
Hello group such good day.
Do I make myself clear:
A1 I have a cell that can enter values manually, for example numbers: 1, 2, 3, etc.
I have the cell B1 has a value of cell A1.
ie if A1 = 2, B1 B1 = 2 should appear.
Now
if I modify: A1 = 3, cell B1 MUST REMAIN 2 (B1 = 2).
and in cell B2 it says the new value A1 (B2 = 3) should appear.

if not understood, there is a way to do with formulas ?, or if any macros,
if I could give an idea of a macro, because I'm not very familiar with macros theme.

thanks for the reply you can give me a greeting.

El martes, 6 de octubre de 2015, 23:11:55 (UTC-5), isabelle escribió:
hi luis vejarano,

voir la solution de MichD sur microsoft.public.fr.excel
see MichD solution on microsoft.public.fr.excel
ver solución MichD en microsoft.public.fr.excel
vedi soluzione MichD su microsoft.public.fr.excel
I'm sorry if I did not recognize your language, please tell me.

isabelle

Hi Isabelle, thank you for answering my question.
My language is Spanish, my original question is in Spanish and translated into French to paste it in that group, but I think that was not properly translated.

I will try to be more specific, I don't know if you can help me.

I have the cell A1 = 1,2,3,... (Values manually entered)
if A1 = "empty", the cells: B1 = "empty" and B2 = "empty".
If A1 = 2, cell B1 = 2, cell B2 = "empty"
If A1 = 3, cell B1 = 2, cell B2 = 3.

That is to say, the first entered value in A1 must be copied to B1, and from the second entered value in A1 should only be changed B2, and B1 must remain the first entered value in A1.

PD: the response of MichD I think that I am not well understood, he takes the column A and I want the cell A1
Thank you very much for the response I hope you can help me. A greeting.

voilà, i hope that i have properly understood, otherwise please tell me

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Set isect = Intersect(Target, Range("A1"))

If Not isect Is Nothing Then
If IsEmpty(Target) Then
Range("B:B").ClearContents
Else
If IsEmpty(Range("B1")) Then
Range("B1") = Target.Value
Else
Range("B" & LastRow) = Target.Value
End If
End If
End If
End Sub

isabelle

Le 2015-10-07 13:29, luis vejarano a écrit :

Hi Isabelle, thank you for answering my question. My language is Spanish, my
original question is in Spanish and translated into French to paste it in
that group, but I think that was not properly translated.

I will try to be more specific, I don't know if you can help me.

I have the cell A1 = 1,2,3,... (Values manually entered) if A1 = "empty", the
cells: B1 = "empty" and B2 = "empty". If A1 = 2, cell B1 = 2, cell B2 =
"empty" If A1 = 3, cell B1 = 2, cell B2 = 3.

That is to say, the first entered value in A1 must be copied to B1, and from
the second entered value in A1 should only be changed B2, and B1 must remain
the first entered value in A1.

PD: the response of MichD I think that I am not well understood, he takes the
column A and I want the cell A1 Thank you very much for the response I hope
you can help me. A greeting.

El miércoles, 7 de octubre de 2015, 14:08:37 (UTC-5), isabelle escribió:
voilà, i hope that i have properly understood, otherwise please tell me

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range, LastRow As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Set isect = Intersect(Target, Range("A1"))

If Not isect Is Nothing Then
If IsEmpty(Target) Then
Range("B:B").ClearContents
Else
If IsEmpty(Range("B1")) Then
Range("B1") = Target.Value
Else
Range("B" & LastRow) = Target.Value
End If
End If
End If
End Sub

isabelle

Hi Isabelle thank you for the answer, see the code if it works.
But the problem I have is that if I enter the first value in A1, cell B1 is fixed (that this perfect!), starting the second value to enter in A1, only you must modify B2.
That is to say, if I enter a third value in A1 must be changed only B2 and that no longer add in B3, B4,... etc.

PD. When clean A1 that only clean B2 and B1 value is the first value entered in A1.
That is to say, which already does not change for anything B1 if I go back to edit A1.

not be if explain me well, to see if I can help, not is much about macros, although if I understand some programming language.

Thank you very much for the help you can give me. A greeting.
Luis.

Hi Luis,

Am Wed, 7 Oct 2015 18:40:42 -0700 (PDT) schrieb luis vejarano:

PD. When clean A1 that only clean B2 and B1 value is the first value entered in A1.
That is to say, which already does not change for anything B1 if I go back to edit A1.

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) < "A1" Then Exit Sub

If Len(Target.Offset(, 1)) = 0 Then
Target.Offset(, 1) = Target
Target.Offset(1, 1) = Target
Else
Target.Offset(1, 1) = Target
End If
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional