Using Date as a Variable

[Les Allan]

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

 

[Mishell]

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" <[email protected]> a écrit dans le message de (e-mail address removed)...
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

 

[FSt1]

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]

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