Simple Macro Error Help

Mike - Nov 15, 2010 at 01:11 PM
 RayH - Nov 16, 2010 at 10:27 PM

I am using Excel 2007 on Windows XP

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


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.

Thank you!

1 reply

In QuarterDateRange sheet
  A        B
1 Qtr from Date	Q#
2 1/1/2010	1
3 4/1/2010	2
4 7/1/2010	3
5 10/1/2010	4
6 1/1/2011	1

In main sheet:
 A   B
1 Quarter	Date
2     1	1/1/2010

The formula to return the quarter is: