Replacing Cell Contents

[Bob Leathers]

Hello Folks

Is there any way via formula to replace the contents of Cell A1 with a
formula written in B1 or any other cell?

Thanks

 

[Frank Kabel]

Hi
no way to achieve this with formulas. Formulas can only return values
but not change other cells

 

[Bob Leathers]

Thanks for your reply.

Is there then a way, via macro, to continually check to see if the value
in cell A1 has changed? If so change the value B1

 

[Dave Peterson]

if the change occurs because of typing, you could use a worksheet_change event.

If the change occurs because a formula returns a different value, you could use
worksheet_calculate.

If you post a few details, I'm sure you'll get a better idea back.

 

[Bob Leathers]

The change does occur because of typing.

If in cell A1 and only A1 if I change the value, I want Cell B1 to say
"Add PMI" if Cell A1 is <20%, User then enters PMI in Cell C1

If cell A1 is >=20% then blank Cell C1

Thanks

 

[Dave Peterson]

First, this might be overkill:

rightclick on the worksheet tab that should have this behavior and select view
code.
Paste this in:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1,C1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
Select Case Target.Address(0, 0)
Case Is = "A1"
If IsEmpty(Me.Range("c1")) Then
Me.Range("b1").Value = "Add PMI"
Else
Me.Range("b1").ClearContents
End If
Case Is = "C1"
If IsEmpty(Me.Range("c1")) = False Then
Me.Range("B1").ClearContents
End If
End Select

errHandler:
Application.EnableEvents = True

End Sub

You may not want the .clearcontents stuff.

 

[Bob Leathers]

Looks like this will work THANKS!