evaluate R1C1 excel function inside of VBA

[white_flag]

Hello

I like to evaluate an excel function inside of VBA that will generate an graphic.
the excel function is like this:

With rTopLeft.Offset(n, m)
formula1 = "INDEX(m_cla,MATCH(rc" & rTopLeft.Column & ",INDEX(m_cla,,1),1),IF(ISNA(MATCH(r" & rTopLeft.Row & "c,INDEX(m_cla,1,))),1,MIN(IF(ISNA(MATCH(r" & rTopLeft.Row & "c,INDEX(m_cla,1,),0)),MATCH(r" & rTopLeft.Row & "c,INDEX(m_cla,1,))+1,MATCH(r" & rTopLeft.Row & "c,INDEX(m_cla,1,),0)))))"
formula2 = "INDEX(pre,IF(ISNA(MATCH((2*r" & rTopLeft.Row & "c+rc" & rTopLeft.Column & "),INDEX(pre,,1))),1,MIN(IF(ISNA(MATCH((2*r" & rTopLeft.Row & "c+rc" & rTopLeft.Column & "),INDEX(pre,,1),0)),MATCH((2*r" & rTopLeft.Row & "c+rc" & rTopLeft.Column & "),INDEX(pre,,1))+1,MATCH((2*r" & rTopLeft.Row & "c+rc" & rTopLeft.Column & "),INDEX(pre,,1),0)))),2)"


.FormulaR1C1 = "=(" & formula1 & "+" & formula2 & ")"

..etc

'graphic

Dim f1 As Double, f2

            
f1 = f1 + Evaluate(formula1)
f2 = f2 + Evaluate(formula2)

Dim ChartObj As ChartObject
Dim ChartSeries As Series
 
Set ChartObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=550, Top:=75, Height:=325)
Set ChartSeries = ChartObj.Chart.SeriesCollection.NewSeries
With ChartSeries
            .Type = xlPie
            .Values = Array(f1, f2)
            .XValues = Array("value 1", "value 2")
            
End With

but because is an R1C1 formula I receive error (type mismatch). can I rewrite formula 1 & formula 2 (just for the graphic part) ? to have the values from excel.

thank you

 

[white_flag]

so it is like this:
this

f1 = f1 + Evaluate(formula1)

become this:

f1 = f1 + Evaluate(Application.ConvertFormula(formula1, xlR1C1, xlA1))

Application.ConvertFormula will convert R1C1 in cell.address.
this fix my problem.

 

[pgc01]

...

f1 = f1 + Evaluate(Application.ConvertFormula(formula1, xlR1C1, xlA1))

Application.ConvertFormula will convert R1C1 in cell.address.
this fix my problem.

Hi

Caveat:

Your R1C1 formula has relative addresses.

If you want to convert a relative formula in R1C1 notation to A1 notation, you must specify to which cell the formula is relative to, or else vba assumes you want the formula relative to the activecell which may not be what you want.

 

[white_flag]

yes you are correct I have the same reference for all "values" in a range. what can be done?

 

[white_flag]

ok. I fix it