I am trying to write my first macro that will simply place the following formula into a cell. The formula as normally written in the cell itself (not a macro version):
=IF(AND('Quarter Date Range'!$B$2<=D2,D2<='Quarter Date Range'!$C$2),1,IF(AND('Quarter Date Range'!$B$3<=D2,D2<='Quarter Date Range'!$C$3),2,IF(AND('Quarter Date Range'!$B$4<=D2,D2<='Quarter Date Range'!$C$4),3,IF(AND('Quarter Date Range'!$B$2<=D2,D2<='Quarter Date Range'!$C$5),4,""))))
This forumla looks at a cell value and compares it to another sheet called "Quarter Date Range" to determine if the value is in Fiscal quarter 1, 2, 3 or 4. My code includes a few other instances where the program places a formula in the cell - they are working fine.
My disfunctional code is:
Set ws as ActiveSheet
r = 2
While ws.cell(r,4) <> ""
ws.Cells(r, 1).Formula = "=IF(AND('Quarter Date Range'!B2<=D" & r & ",D" & r & "<='Quarter Date Range'!C2),1,IF(AND('Quarter Date Range'!B3<=D" & r & ",D" & r & "<='Quarter Date Range'!C3),2,IF(AND('Quarter Date Range'!B4<=D" & r & ",D" & r & "<='Quarter Date Range'!C4),3,IF(AND('Quarter Date Range'!B2<=D" & r & ",D" & r & "<='Quarter Date Range'!C5),4,""))))"
r = r + 1
Wend
As you can see I have already replaced the cell references for the active sheet with the code " & r & ", which replaces the standard fill down function of the forumla. This is working fine in the other cell forumals in the code. A collegue suggested I use " & Chr(39) & " to replace the single quote charater ('). But that did not work also.
There is probably a much better way to do this, but I would like to know why this is not working. If you would also like to suggest a different method, I would like to see that too.
