Formula not working when put inside VBA macro

**Chrispelletier** · 01-21-2013, 09:43 AM

Hello,

I am using 2 formulas to report the quarters and fiscal years of dates found in column A (starting at A2) in Excel 2010. The formulas are working perfectly in Excel but when I try to incorpoate them in a VBA macro, the first formula works fine but the second fomula does not work (I get run time errror 13 type mismatch). I think the error might be that VBA does not accept &"-" in a formula? Is there a work around this? Any help would be appreciated. Here is my VBA code:

Sub testformula()

With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 1)
.Formula = "=CHOOSE(MONTH(A2),4,4,4,1,1,1,2,2,2,3,3,3)"
.Value = .Value
End With

With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
.Formula = "=IF(MONTH(A2)<4,YEAR(A2)-1&"-"&RIGHT(YEAR(A2),4),YEAR(A2)&"-"&RIGHT(YEAR(A2)+1,4))"
.Value = .Value
End With

End Sub

**benishiryo** · 01-21-2013, 09:53 AM

hi Chrispelletier, welcome to the forum. do remember to put the code tags when you use codes here. it's the # sign. it should look like below:

Sub test()

With Range("A2", Range("A" & Rows.Count).End(xlUp)).Offset(, 2)
.Formula = "=IF(MONTH(A2)<4,YEAR(A2)-1&"" - ""&RIGHT(YEAR(A2),4),YEAR(A2)&"" - ""&RIGHT(YEAR(A2)+1,4))"
.Value = .Value
End With

End Sub

when putting in formulas with double quotes, you must repeat them. otherwise, VBA thinks you are ending the formula since you started with
"=IF