Place in A1 the value generated by formula in B1

[Trebor Retrac]

Place in A1 the Value generated by a formula in B1. A1 can not contain a
formula.
B1 may, or may not, display its formula result.

 

[Jim Thomlinson]

Place this code into the code for the sheet you want. Right click on the tab
and choose view code..

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Range("A1").Value = ActiveSheet.Range("B1").Value
End Sub

HTH

 

[Trebor Retrac]

Thanks Jim:

The routine worked to transfer the value.
However, once started , the routine appears to not stop after the first
ececution.
I have to use the escape key to break the routine and then end it.
Ther must be some way to make the routine execute only once for each change
of value in "B1".
I am not familiar with Visual Basic but am quite familiar with C++.
I modified the cell declarations to array values "A1:C160" and "F1:H160",
which is what I really want to do, and still the got the eternal looping of
the routine.

Thanks
Bob Carter
Douglas, MA

 

[Montrose77]

Try something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = $B$1
ActiveSheet.Range("A1") = ActiveSheet.Range("B1")
End If
End Sub

 

[Trebor Retrac]

Thanks to you and Jim

My looping problem went away ???????
Further search for "macro protection" found the reason for the looping.
Had to do with locking and protection.
The search also explained how to protect macro/VBA code.

Both of the approaches ,modified for arrays, did what I wanted to do.
I also use macros, with control keys, to execute Copy/Paste Special/Values
to move formula generated Golf Player arrays to other non-formula containing
arrays.
This is a comprehensive Golf scoring program.

Thanks for all the help.

Bob Carter
Douglas,MA