Refer to ActiveCell

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.
 
H

Homey

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.
 
C

ChrisR

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
 
G

Gord Dibben

If all you want is Z100 to return the activecell contents

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


Gord
 
C

ChrisR

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...
 
Top