PDA

View Full Version : returning the reference to a cell, not the value in the cell



blahblahblah
02-04-2010, 03:28 PM
Is there any way to return the cell reference, as opposed to the value that is in the cell?

For example:

Say the value in cell E6 is 5 and that E6 is the active cell.

Suppose I want to set a variable equal to this cell. I want the variable to equal E6, not 5. How do I do that?

tpoynton
02-04-2010, 04:15 PM
Sub blahblahblah()
Dim sActiveCl As String
sActiveCl = ActiveCell.Address(RowAbsolute:=False, columnabsolute:=False)
MsgBox sActiveCl
End Sub

EDIT - if you want the range to use as a range...


Sub blahblahblah2()
Dim rActiveCl As Range
Set rActiveCl = ActiveCell
rActiveCl.Value = "blah"
End Sub

arkusM
02-05-2010, 02:09 PM
=E5 or = $E5 or =$E$5 or =E$5... oh unless you mean via VB. In which case see above. :grinhalo: