CurrentCell

[Robin Clay]

Greetings !

I have this code:-

Public Function RefValue(RefCell As Range)
' If this is called from Cell K75, RefCell is A75,
' and the formula in RefCell is "=A23",
' then this routine returns the value in Cell K23
Dim ThisCol As Integer, ThatCol As Integer
Dim ThatRow As Long, RefCellForm As String, myText
ThisCol = ActiveCell.Column
ThatRow = RefCell.Row
ThatCol = RefCell.Column
myText = Cells(ThatRow, ThatCol).Formula
myText = Mid$(myText, 3)
If IsALetter(Left(myText, 1)) Then
myText = Mid$(myText, 2)
End If
RefValue = Cells(myText, ThisCol).Value
End Function

This works just FINE... except...
It does NOT work when I re-calculate,
for it addresses the ACTIVE cell,
whereas it should access the CURRENT cell,
i.e. that from which the call is made.

Is there such a function ?

Or how else can I get round this challenge !


Regards

Robin

 

[Tom Ogilvy]

Public Function RefValue(RefCell As Range)
' If this is called from Cell K75, RefCell is A75,
' and the formula in RefCell is "=A23",
' then this routine returns the value in Cell K23
Dim rng as Range
Dim ThisCol As Integer, ThatCol As Integer
Dim ThatRow As Long, RefCellForm As String, myText
set rng = Application.Caller
ThisCol = rng.Column
ThatRow = RefCell.Row
ThatCol = RefCell.Column
myText = Cells(ThatRow, ThatCol).Formula
myText = Mid$(myText, 3)
If IsALetter(Left(myText, 1)) Then
myText = Mid$(myText, 2)
End If
RefValue = Cells(myText, ThisCol).Value
End Function