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

Hi Claus, thank you very much for your reply.
Well, the code that send me what add to the Isabelle, I made a change and I was thus:

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("B2").ClearContents
Else
If IsEmpty(Range("B1")) Then
Range("B1") = Target.Value
Else
Range("B" & LastRow) = Target.Value
End If
End If
End If
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

If I enter the first value in A1, is copied to B1 and B2 (the first single value would that you copy in B1).
If I enter the second, third, fourth,... value in A1, only modifies B2, (is perfect).
If I delete A1, only clears B2, (is also perfect, this is how I want to)

But if you realize, if I enter a third, fourth, fifth,... value in A1, still adding in B3, B4, B5. (that's what I don't want to).

In the code that I step Isabelle in this line:

LastRow = Range("B" & Rows.Count).End(xlUp).Row + 1

I think that it refers to the last row with data, it will be so that you are adding and continuously adding, thats how I can not correct, not is much of the VBA, see if you can help me appreciate it much.

PD. your code the Add of Isabelle not is if is well placed, as I repeat, do not know much of the VBA, see if you give me a hand this niggle.
Regards.
Luis.

Hi Luis,

Am Thu, 8 Oct 2015 09:36:45 -0700 (PDT) schrieb luis vejarano:

If I enter the first value in A1, is copied to B1 and B2 (the first single value would that you copy in B1).
If I enter the second, third, fourth,... value in A1, only modifies B2, (is perfect).
If I delete A1, only clears B2, (is also perfect, this is how I want to)

But if you realize, if I enter a third, fourth, fifth,... value in A1, still adding in B3, B4, B5. (that's what I don't want to).

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
Cells(Rows.Count, 2).End(xlUp)(2) = Target
ElseIf Target = "" Then
Cells(Rows.Count, 2).End(xlUp) = Target
Else
Cells(Rows.Count, 2).End(xlUp)(2) = Target
End If
End Sub


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

Hello Claus Thank you once again
Look, the first code you have posted works perfectly!!!.

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

The mistake I made is copy above the code of Isabelle, why it was added to the other cells.
I just wanted to work in cell B1 and B2, nothing more.
and your first code was useful for me.

Now to suit my needs, that is to say:

If I want the cell A1 (where income values), this in sheet1 and that the results appear in the sheet2 (B1 and B2), as I have to do the reference in your code?
I'm a newbie in the topic of VBA.

Thank you for your help.
Luis.

Hi Kuis,

Am Thu, 8 Oct 2015 12:58:00 -0700 (PDT) schrieb luis vejarano:

If I want the cell A1 (where income values), this in sheet1 and that the results appear in the sheet2 (B1 and B2), as I have to do the reference in your code?

try in the code module of sheet1:

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

With Sheets("Sheet2")
If Len(.Range("B1")) = 0 Then
.Range("B1") = Target
.Range("B2") = Target
Else
.Range("B2") = Target
End If
End With
End Sub


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

Perfect Claus, just what I wanted.
Many thanks for the help
Regards
Luis

Hi Claus
I was most helpful your code. However, I want to bother you with one last concern:

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

With Sheets("Sheet2")
If Len(.Range("B1")) = 0 Then
.Range("B1") = Target
.Range("B2") = Target
Else
.Range("B2") = Target
End If
End With
End Sub

How can I do that your code will run in multiple cells, that is to say:
I have the sheet1 cell A1 (where income values), and the sheet2 where it runs (B1 and B2).
Now,
I want to use in sheet1 cell D1 (where income other values), and in the sheet2 to run on (E1 and E2).

Bone, use a code on sheet1 in two cells (A1 and D1).

Obviously, if the sheet1 a third cell F1 (for example), would be implemented in (G1 and G2).

I guess it's repeating the code, but I've tried trying to understand it, but I could not the expected result.

I hope you can help me and thank you for everything
Regards
Luis.

Hi Luis,

Am Fri, 9 Oct 2015 09:22:24 -0700 (PDT) schrieb luis vejarano:

How can I do that your code will run in multiple cells, that is to say:
I have the sheet1 cell A1 (where income values), and the sheet2 where it runs (B1 and B2).
Now,
I want to use in sheet1 cell D1 (where income other values), and in the sheet2 to run on (E1 and E2).

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1,D1")) Is Nothing _
Then Exit Sub

With Sheets("Sheet2")
Select Case Target.Address(0, 0)
Case "A1"
If Len(.Range("B1")) = 0 Then
.Range("B1") = Target
.Range("B2") = Target
Else
.Range("B2") = Target
End If
Case "D1"
If Len(.Range("E1")) = 0 Then
.Range("E1") = Target
.Range("E2") = Target
Else
.Range("E2") = Target
End If
End Select
End With
End Sub


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

Hi Claus thank you for your answer.
Now if everything is perfect, it's just what i wanted.
I served a lot of your code.
Even, I tried for a third value (cell F1), I made the modification and works great.

Thank you for everything
Regards
Luis