Duplication

[Wonderer]

I need to know how to have two cells contain the same information, on the
same sheet, so that when I change on cell, the other will reflect that
change. How do I do that?

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

 

[Max]

I need to know how to have two cells contain the same information, on the
same sheet, so that when I change on cell, the other will reflect that
change. How do I do that?

If you put in say, F1: =IF(A1="","",A1)
F1 will reflect the input in A1.
If A1 is cleared, F1 will display a blank: ""

 

[William Benson]

Wondereer is looking for a change in EITHER cell getting reflected in the
other cell. In other words, there can be no dependencies other than what VBA
imposes through code.



Option Explicit

'Be Sure You Have Defined The Range FirstRef and
'SecondRef appropriately

Private Sub Worksheet_Change(ByVal Target As Range)
'Code to make changes in one reference range get
'automatically made in another, bi-directionally

Static IgnoreMyChange As Boolean

If IgnoreMyChange Then
IgnoreMyChange = False
Exit Sub
End If

If Replace$(Target.Address, "$", "") = _
Replace$(Range("firstref").Address, "$", "") Then
'Target was firstref, set secondref
IgnoreMyChange = True
Range("SecondRef").Value = Range("FirstRef").Value
IgnoreMyChange = False
Exit Sub
End If

If Replace$(Target.Address, "$", "") = _
Replace$(Range("SecondRef").Address, "$", "") Then
'Target was firstref, set secondref
IgnoreMyChange = True
Range("FirstRef").Value = Range("Secondref").Value
IgnoreMyChange = False
Exit Sub
End If
End Sub

 

[William Benson]

one of my comment lines repeats -

2nd instance of ''Target was firstref, set secondref' should say

'Target was SecondRef, set FirstRef

Sorry

 

[Guest]

Assuming A1 and D1 are the cells and you want to allow change in either cell:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
End Sub

Regards,
Greg

 

[Max]

My apologies for the mis-read.

 

[Wonderer]

Ok, what I have is cell A1 and F1 are used in equations. They need to be
exactly the same, at all times. I want it simply so that I can change the
amount in A1 and it will automatically change the amount in F1 and the
subsequent equation will reflect that change. I am not "literate" in the
code and terminology, so please explain it so a beginner can understand.

Thank you

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

 

[William Benson]

Steve, If all you want is F1 to be dependent on A1, you just make the
formula in F1 say "=A1". If on the other hand, you want something which
will cause either cell to automatically update when the other is changed ...
then the Worksheet_Change event code which I gave earlier (pasted again
below here) should do the trick. If you need help with getting to that point
I would be happy to explain more, but will not go down that path if you are
not interested. I am going on vacation shortly as well, others might help
you further.

Regards,

Bill

Option Explicit
'Be Sure You Have Defined The Range FirstRef and
'SecondRef appropriately

Private Sub Worksheet_Change(ByVal Target As Range)
'Code to cause a change in one reference range to be automatically
'carried over to another, in either direction.

Static IgnoreMyChange As Boolean ' Don't want steps in this routine to fire
'when we set second
cell = first, a static
'variable is one way
to keep tabs on
'what's happening.

If IgnoreMyChange Then 'This sub was called through the VBA operation, not
user input
IgnoreMyChange = False
Exit Sub
End If

If Replace$(Target.Address, "$", "") = _
Replace$(Range("firstref").Address, "$", "") Then
'Target was firstref, set secondref to this
IgnoreMyChange = True
Range("SecondRef").Value = Range("FirstRef").Value
IgnoreMyChange = False
Exit Sub
End If

If Replace$(Target.Address, "$", "") = _
Replace$(Range("SecondRef").Address, "$", "") Then
'Target was SecondRef, set FirstRef to this
IgnoreMyChange = True
Range("FirstRef").Value = Range("Secondref").Value
IgnoreMyChange = False
Exit Sub
End If
End Sub

 

[William Benson]

And Greg's code was way better than mine by the way ... nice 'n' elegant ...
I'll get there someday on this slow march from power-user to programmer ...

"Give a man a fish, feed him for a day. Teach him to fish, and he give's up
programming"

 

[Max]

Steve, If all you want is F1 to be dependent on A1,
you just make the formula in F1 say "=A1". ..

It certainly sounds that way to me. So perhaps the initial interp on the OP
and suggestion given wasn't that far off, afterall ..

 

[William Benson]

Your powers go way beyond Excel Guru, Max -- to mind reader!

Cheers.

 

[Max]

Ok, what I have is cell A1 and F1 are used in equations.

They need to be exactly the same, at all times. ...

What perhaps is not so clear from the OP's response to Greg is why the
equations cannot be (re)structured to point to only A1 ?

 

[Guest]

The code I gave you allows you to change or delete either the value in A1 or
D1 and the change will be reflected in the other cell - i.e. the behavior is
bidirectional. Although it doesn't sound like this is what you want, I will
first describe how to implement it assuming you are not familiar.
Instructions:

1) Change the cell references to suit in the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
End Sub

2) Copy the code.
3) Hold the mouse pointer over the sheet tab and right mouse-click.
4) Select View Code. This will take you to the sheet's code module.
5) Paste the code.
6) Test it out: Change or delete the values in either cell. Note that the
code also alows you to select multiple cells inclusive of either of the above
and delete them and it will still work. It was deliberately written to handle
this situation.

All of the above said, it sounds as though you only need a simple worksheet
formula (or formulae). If you only intend to be changing the value in cell A1
and never in D1 but you want D1 to reflect A1's value, then, as the others
have already provided, the formula: "=A1" or "=If(A1 = "", "", A1) should do.
If this doesn't do what you need then I suggest posting the formula(e) and
cell address(es).

Regards,
Greg

 

[Tom Ogilvy]

Greg - just a heads up:

If I modify your code with a counter variable to see how many times it is
called, then it looks like this

Private Sub Worksheet_Change(ByVal Target As Range)
Static cnt As Long
If Not Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
cnt = cnt + 1
Debug.Print cnt
End Sub

then I change the value of A1; in the debug window, the last couple of lines
are:

200
201
202
203
204
205

(thank goodness it finally stops - <g>)

so you might want to make it like

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents:=False
If Intersect(Target, Range("A1")) Is Nothing Then
Range("D1") = Range("A1")
ElseIf Not Intersect(Target, Range("D1")) Is Nothing Then
Range("A1") = Range("D1")
End If
ErrHandler:
Application.EnableEvents:=True
End Sub

 

[Wonderer]

Thanks to all of you for your help. Initially, I had tried using =a1 but for
some reason it would not work. That is why I posted here. When I went back
and tried it again after reading some replies, it worked. I really do
appreciate the responses though, because some groups get no response at all.

--

Steven Shelton

There are two secrets to success in life
1) Never tell anybody everything you know

 

[Guest]

Thanks for the heads up Tom. I know better but it worked well enough that I
didn't give it a second thought. You never cease to amaze me with how sharp
you are. I sure wish I had your gift.

Best regards,
Greg