Date calculation difficulties in macro (2002 SP/2)

Topic

New Reply

The following macro works as it was designed:

Sub Test3()
Dim ReportEndDate As Date
With Worksheets(“Paste New Data Here”)
ReportEndDate = .Cells(.UsedRange.Rows.Count – 1, 2)
End With
ActiveCell.FormulaR1C1 = _
“=IF(AND(RC[-1]>=Today()-30,RC[-1]<TODAY()),RC[-1],"""")"

End Sub

The variable ReportEndDate gets its date value from a specific cell on the Paste New Data Here worksheet. The IF/AND function calculates correctly.

However, my problem is that when I attempt to use the variable ReportEndDate in place of the TODAY() function, I get run time errors. What I am trying to do is to calculate the ReportEndDate against the date in the previous column. When I do this using the TODAY() function it works. When I try to do this using ReportEndDate , it doesn't.

Can anyone tell me what I'm doing wrong?

Thanks in advance…

Reply | Quote

Replies

You cannot put the variable name in the formula, becasue it is not a worksheet function. You can use the value of the variable as follows:

ActiveCell.FormulaR1C1 = _
“=IF(AND(RC[-1]>=” & CLng(ReportEndDate) & “-30,RC[-1]<" & CLng(ReportEndDate) & "),RC[-1],"""")"

Reply | Quote

Hans,

Thank you for your solution. It works perfectly.

But perhaps you can explain the CLng(ReportEndDate) structure to me. What is CLng?

Also, what would be the best way to format these numbers as dates?

Thanks again,

Reply | Quote

CLng is a function that converts its argument to a Long Integer (4 bytes) numeric value. Since you are creating the formula as a string, you would have a problem if you concatenated just the variable. Excel would interpret 10/08/02 as a division (or in my regional settings, 08-10-2002 as a subtraction). Internally, dates are stored as a number. To be precise, the number of days since December 31, 1899. CLng converts the value of the variable to the correct numeric value.

Do you want to format cells as dates in code? In that case, you can use something like

ActiveCell.NumberFormat = “mm/dd/yy”

Adapt as needed. The codes used are the same as those for custom date formats in a worksheet.

Reply | Quote

Hans, thanks again for your on-the-money detailed explanation.

Right after I posted my last message I found CLng in VBA help and did some reading on it, so between that and your explanation, I think I have it. I’ve found this area of VBA to be difficult to penetrate; there doesn’t seem to be much in the way of help on these issues, so it looks like a matter of just plugging away until you figure it out (or, as in this case, until someone shows you).

I really appreciate not only your coding suggestion but also your very clear explanations of these things.

Regards,

Reply | Quote