I think you have a couple problems.
You're using the activecell's address in your formula in that cell.
It's a lot like putting: =sum(a1:b1) in A1. You're going to get a circular
reference error.
And if VR evaluates to $H$9, you don't want to use .formulaR1c1 in your
assignment. .Formula will work ok.
Option Explicit
Sub testme()
Dim VR As String
Range("H9").Activate
VR = ActiveCell.Address(0, 0)
ActiveCell.Formula = "=Choose(Month(" & VR & ") + 1, ""jan"", ""feb""," _
& """mar"", ""apr"", ""may"", ""jun"", ""jul""," _
& """aug"", ""sep"", ""oct"", ""nov"", ""dec""," _
& """jan"", ""feb""," _
& """mar"", ""apr"", ""may"", ""jun"", ""jul""," _
& """aug"", ""sep"", ""oct"", ""nov"", ""dec"")"
End Sub
resulted in that circular reference for me.
Take a look at .address in vba's help. You'll see that those parms (0,0) tell
the code to use absolute references or not.
I changed the location of the formula and used a couple of alternate formulas.
They looked like they evaluated to the same:
Option Explicit
Sub testme2()
Dim VR As String
Range("H9").Activate
VR = ActiveCell.Address(0, 0)
Range("a1").Formula = "=Choose(Month(" & VR & ") + 1, ""jan"",""feb""," _
& """mar"",""apr"",""may"",""jun"",""jul""," _
& """aug"",""sep"",""oct"",""nov"",""dec"")"
Range("a2").Formula = "=lower(TEXT(DATE(YEAR(" & VR & "),MONTH(" _
& VR & ")+1,1),""mmm""))"
End Sub
And one more thing if you find the double quotes as confusing as I do.
Write your formula using a unique character for the ". (I used @.)
Option Explicit
Sub testme03()
Dim VR As String
Dim FormulaStr As String
Range("H9").Activate
VR = ActiveCell.Address(0, 0)
FormulaStr = "=Choose(Month(" & VR & ") + 1, @jan@,@feb@," _
& "@mar@,@apr@,@may@,@jun@,@jul@," _
& "@aug@,@sep@,@oct@,@nov@,@dec@)"
FormulaStr = Application.Substitute(FormulaStr, "@", """")
Range("a1").Formula = FormulaStr
End Sub
This was a tip that Tom Ogilvy made to a similar post. I find it much easier to
work with quoted strings now!
