Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date calculation difficulties in macro (2002 SP/2)

    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...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation difficulties in macro (2002 SP/2)

    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],"""")"

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation difficulties in macro (2002 SP/2)

    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,

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date calculation difficulties in macro (2002 SP/2)

    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.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date calculation difficulties in macro (2002 SP/2)

    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,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •