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

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

hi
try it this way....
Sub testit()
Dim sCripDate As String
sCripDate = "2007,10,10"

With Worksheets("sheet1")
.Range("D8").Value = sCripDate '<--------
End With
End Sub

regards
FSt1

"Les Allan" wrote:

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 didnt help. I am using XL 2007, but the code must
work down to XL 2002. Any help will be much appreciated.

Regards,
Les Allan
.

Thanks Mishell and Fst1. I spent ages trying to figure out what was
wrong.

Your solution worked Fst1; however, it doesn't put the date in the
cell in the right format. Mishell's solution worked like a treat.
Thanks again to both of you.

Does anyone know of any VBA developers in Melbourne, Australia?

Regards,
Les