Using DATE in VBA, gives error

[Andreas Palm (HP C&I .NET)]

Range("L" & c).Select
With Selection
.FormulaR1C1 = "DATE(YEAR(""" & t & """);" & "MONTH(""" & t & """)+" & b & " ;" & "DAY(""" & t & """))"
End With


What I try to do is this

.FormulaR1C1 = "=DATE(YEAR(""" & t & """);" & "MONTH(""" & t & """)+" & b & " ;" & "DAY(""" & t & """))"


However as soon as I put "=" in the code I get an error. Without the "=" the cells get filled with this:


DATE(YEAR("01.01.2005");MONTH("01.01.2005")+2 ;DAY("01.01.2005"))

if in the cell I put the "=" in front of this text I get exactly what I want => 01.03.2005


So why do I get an error in the code, if when I change the code later manually the formula is correct.


best regards
andreas

[Tom Ogilvy]

VBA uses US delimiters and so forth for formula and formulaR1C1 so use a
comma instead of a semicolon

t = Application.Replace(t,".","/")
.FormulaR1C1 = "=DATE(YEAR(""" & t & """)," & "MONTH(""" & t &
""")+" & b & " ," & "DAY(""" & t & """))"

You also might have problems with month/day sequencing.

--
Regards,
Tom Ogilvy

"Andreas Palm (HP C&I .NET)" <[email protected]> wrote in message
news:[email protected]...
> Range("L" & c).Select
> With Selection
> .FormulaR1C1 = "DATE(YEAR(""" & t & """);" & "MONTH(""" & t &
""")+" & b & " ;" & "DAY(""" & t & """))"
> End With
>
>
> What I try to do is this
>
> .FormulaR1C1 = "=DATE(YEAR(""" & t & """);" & "MONTH(""" & t &
""")+" & b & " ;" & "DAY(""" & t & """))"
>
>
> However as soon as I put "=" in the code I get an error. Without the "="
the cells get filled with this:
>
>
> DATE(YEAR("01.01.2005");MONTH("01.01.2005")+2 ;DAY("01.01.2005"))
>
> if in the cell I put the "=" in front of this text I get exactly what I
want => 01.03.2005
>
>
> So why do I get an error in the code, if when I change the code later
manually the formula is correct.
>
>
> best regards
> andreas
>