How to use/evaluate another Cell Formula in R1C1 style

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a formula to evaluate another cell formula from the
formula caller cell address.
Supose a have
a b
1 10 30
2 20 40
3 =A1+A2 =udfEvaluate(A3)

the user-defined-formula udfEvaluate should retieve B1+B2=70, i.e., A3 cell
formula evaluated from its perspective = R[-2]C+R[-1]C

P.S. The real formula is huge and does not fit in name-defined.
 
Function udfEvaluate(ByRef rng As Range)
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(rng.Offset(0, -1).Formula, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
udfEvaluate = rng.Parent.Evaluate(sF1)
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)
 
Thanks Bob, I just removed the offset(0,-1) and its is working fine.

"Bob Phillips" escreveu:
Function udfEvaluate(ByRef rng As Range)
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long
With Application
iRow = rng.Row
iColumn = rng.Column
sF1 = .Substitute(rng.Offset(0, -1).Formula, "ROW()", iRow)
sF1 = .Substitute(sF1, "COLUMN()", iColumn)
sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
End With
udfEvaluate = rng.Parent.Evaluate(sF1)
End Function


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

Caio Milani said:
I am trying to create a formula to evaluate another cell formula from the
formula caller cell address.
Supose a have
a b
1 10 30
2 20 40
3 =A1+A2 =udfEvaluate(A3)

the user-defined-formula udfEvaluate should retieve B1+B2=70, i.e., A3 cell
formula evaluated from its perspective = R[-2]C+R[-1]C

P.S. The real formula is huge and does not fit in name-defined.
Click to expand...
Click to expand...
 
Back
Top