# Simple Macro Error Help

Solved/Closed
-
RayH -
Hello,

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

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.

Thank you!

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
etc
```

The formula to return the quarter is:
=VLOOKUP(B2,QuarterDateRange!A\$2:B\$6,2)