I have the following formula in cell A3 of Sheet1.
=CONCATENATE("For the ",IF(MONTH('Sheet2'!J9)=12,"Twelve",IF(MONTH('Sheet2'!J9)=9,"Nine","Six"))," Months Ended ",'Sheet2'!J9)
Entering this formula gives the following output: For the Twelve months Ended December 31, 2020.
However, when I use the Application.ConvertFormula function, cell A3 is converted to #VALUE!.
Code
Dim rng1 As Range
Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set rng1 = ws1.Range("A3")
If rng1.HasFormula Then
rng1.Formula = Application.ConvertFormula(rng1.Formula, 1, 1, 1)
End If
Can you help me figure out why do I get #VALUE! as output?