Using Date as a Variable

L

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
 
M

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
 
F

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
 
L

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top