Convert ConvertFormula xlR1C1 to xlA1

[makati1026]

The next 3 lines of code works fine. However, the code at bottom returns "error 2015" or Type mismatch 13 at runtime.

Working_FormulaR1C1 = "=MATCH(R23C1,IF((Sheet1!R5C2:R100C2=R23C1),Sheet1!R5C2:R100C2),0)"
Testing = Application.ConvertFormula(Working_FormulaR1C1 , xlR1C1, xlA1, xlAbsolute, ActiveCell)
Result_WORKS = Application.Evaluate(Testing)

This code fails. Only difference is the code in RED. I need to convert an R1C1 array formula into A1 style and evaluate result. I tried multiple time so Evaluate R1C1 formula it always fails but A1 style works. However, I can't convert the following code to A1 style without error. What is wrong?
ERROR_FormulaR1C1 = "=MATCH(R23C1,IF((Sheet1!R5C2:R100C2=R23C1)*(Sheet1!R5C6:R100C6>=R5C6),Sheet1!R5C2:R100C2),0)"
Testing2 = Application.ConvertFormula(Working_FormulaR1C1 , xlR1C1, xlA1, xlAbsolute, ActiveCell)
Result_ERROR = Application.Evaluate(Testing2)

 

[Worf]

Welcome to the Board

The code below worked for me. The issue may be related to your data set, you can post a link to a test workbook and we will look into it.

Sub Makati()
Dim wk$, errr$


wk = "=MATCH(R23C1,IF((Sheet1!R5C2:R100C2=R23C1),Sheet1!R5C2:R100C2),0)"


MsgBox Evaluate(Application.ConvertFormula(wk, xlR1C1, xlA1, 1, ActiveCell))


errr = "=MATCH(R23C1,IF((Sheet1!R5C2:R100C2=R23C1)*(Sheet1!R5C6:R100C6>=R5C6),Sheet1!R5C2:R100C2),0)"


MsgBox Evaluate(Application.ConvertFormula(errr, xlR1C1, xlA1, 1, ActiveCell))


End Sub

 

[makati1026]

Hi Worf ! Yea, definitely a data set issue. After looking at your reply I realized the code failed because I needed to reference a second sheet and without out that it would never work
WorksNow = "=MATCH(Sheet2!R23C1,IF((Sheet1!R5C2:R100C2=Sheet2!R23C1)*(Sheet1!R5C6:R100C6>=Sheet2!R5C6),Sheet1!R5C2:R100C2),0)"