Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentCell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentCell
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 -- Regards, Tom Ogilvy "Robin Clay" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|