Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro formula using MYDATE not working
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro formula using MYDATE not working
The end result should be to get the following formula
=IF(S2DATE(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 "Bricktop" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro formula using MYDATE not working
Thanks Alok! - It worked like a charm and thanks for the explanation.
"Alok" wrote: The end result should be to get the following formula =IF(S2DATE(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 "Bricktop" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA formula in macro not working | Excel Discussion (Misc queries) | |||
Formula within a Macro not working as expected. | New Users to Excel | |||
macro not working | Excel Discussion (Misc queries) | |||
macro was working, now it's not working | Excel Programming | |||
Macro working in Excel 2003; not working in Excel 2000 | Excel Programming |