Evaluate R1C1 Formula VBA

[mlo356]

Hi All,

I am trying to use VBA to evaluate an R1C1 style formula and assign the result to a variable instead of having the macro place the formula in a cell, assign a variable to the result, then deleting the content of the cell. The formula is:

ActiveCell.FormulaR1C1 = "=MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),R[1],0)"

R[1] refers to the entire row 3.

Looking at an example I found on this forum, I tried:

Dim x
Dim y
x = "MATCH(DATE(YEAR(TODAY()),MONTH(TODAY())+1,0),R[1],0)"
y = Application.Evaluate(Application.ConvertFormula(x, xlR1C1, xlA1))

I am probably looking at this all wrong. Any help would be appreciated.

Thanks!!