Using Date as a Variable

  • Thread starter Thread starter Les Allan
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top