Macro formula using MYDATE not working

[Guest]

I am trying to put a formula in R2 that says - =if(S2 > MYDATE,1,if(s2 =
mydate,3,2))

Here is the code I have:
Dim CURRDATE As Date
Dim MYDATE As Date
CURRDATE = Date - 6 '10/20/2005
MYDATE = Format(CURRDATE, "mm/dd/yyyy")
Columns("S:S").Select
Selection.NumberFormat = "mm/dd/yyyy"
Range("R2").FormulaR1C1 = ("=IF(RC[1]>" & MYDATE & ",1,IF(RC[1]=" &
MYDATE & ",3,2))")

For some reason, R2 is always returning a result of 1 when it should return
the result of 3 or 2 when the date equals or falls below the MYDATE?

Any Suggestions?
Thanks in Advance!

 

[Guest]

The end result should be to get the following formula

=IF(S2>DATE(2005,10,20),1,IF(S2=DATE(2005,10,20),3,2))

This is because in your formula 10/20/2005 is being interpreted as
successive divisions and is leading to a very small fraction and this
fraction is always less than the number in S2 and hence you are getting a 1
as the result of the formula.

Hence try to generate the formula string above or try the easier option below

Range("R2").FormulaR1C1 = ("=IF(RC[1]>Datevalue(""" & MYDATE &
"""),1,IF(RC[1]=Datevalue(""" & MYDATE & """),3,2))")

Alok

 

[Guest]

Thanks Alok! - It worked like a charm and thanks for the explanation.

The end result should be to get the following formula

=IF(S2>DATE(2005,10,20),1,IF(S2=DATE(2005,10,20),3,2))

This is because in your formula 10/20/2005 is being interpreted as
successive divisions and is leading to a very small fraction and this
fraction is always less than the number in S2 and hence you are getting a 1
as the result of the formula.

Hence try to generate the formula string above or try the easier option below

Range("R2").FormulaR1C1 = ("=IF(RC[1]>Datevalue(""" & MYDATE &
"""),1,IF(RC[1]=Datevalue(""" & MYDATE & """),3,2))")

Alok

I am trying to put a formula in R2 that says - =if(S2 > MYDATE,1,if(s2 =
mydate,3,2))

Here is the code I have:
Dim CURRDATE As Date
Dim MYDATE As Date
CURRDATE = Date - 6 '10/20/2005
MYDATE = Format(CURRDATE, "mm/dd/yyyy")
Columns("S:S").Select
Selection.NumberFormat = "mm/dd/yyyy"
Range("R2").FormulaR1C1 = ("=IF(RC[1]>" & MYDATE & ",1,IF(RC[1]=" &
MYDATE & ",3,2))")

For some reason, R2 is always returning a result of 1 when it should return
the result of 3 or 2 when the date equals or falls below the MYDATE?

Any Suggestions?
Thanks in Advance!