You must put the variable outside of the quotes,

Dim sCripDate As String
sCripDate = "2007,10,10"

With Worksheets("Cost")
.Range("D9").Formula = "=DATE(" & sCripDate & ")"
End With

Mishell

"Les Allan" a écrit dans le message de news:
...
I have cell contents and formulae in cells with fixed dates in them. I
need to create other versions of the workbook with different dates. So
I created a date variable. But when I run the procedure to replace the
fixed dates with another date, I get an error message:

Run-time error '1004':
Application-defined or object-defined error

Here is a simple example of what I have in the code.

Dim sCripDate As String
sCripDate = "2007,10,10"

With Worksheets("Cost")
.Range("D8").Value = "=DATE(sCripDate)"
End With

I must be doing something really silly. I tried setting the data type
to Date and that didn’t help. I am using XL 2007, but the code must
work down to XL 2002. Any help will be much appreciated.

Regards,
Les Allan