Refer to ActiveCell

  • Thread starter Thread starter ChrisR
  • Start date Start date
C

ChrisR

I want a given cell eg z100 to constantly - or at each recalculation - to
hold the contents - or reference - of ActiveCell (so a text box can highlight
it.). My code does it but only once at each run of the module, then it sticks
or throws an error.
 
address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))


|I want a given cell eg z100 to constantly - or at each recalculation - to
| hold the contents - or reference - of ActiveCell (so a text box can
highlight
| it.). My code does it but only once at each run of the module, then it
sticks
| or throws an error.
 
Homey suggested - I think -

Sub ActiveCell()
'
' ActiveCell Macro
' Macro recorded 28/09/2009 by Sheffield City Council
'

address of active cell at calc: =CELL("address")

contents: =INDIRECT(CELL("address"))

End Sub


But as a Macro this doesn't do anything except generate an error - "Compile
error Syntax error". I want it to paste the contents or reference of
ActiveCell into Z100, whenever I recalc (or when I run a Macro?)

Sorry to be naiive.

Chris
 
If all you want is Z100 to return the activecell contents

Sub test()
Range("Z100) = ActiveCell.Value
End Sub


Gord
 
Thanks a million. That does it.

Chris

Gord Dibben said:
If all you want is Z100 to return the activecell contents

Sub test()
Range("Z100) = ActiveCell.Value
End Sub


Gord
Click to expand...
 
Back
Top