Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Copy conditional date (xppro mso2003)

    Hi

    I am wanting to produce a report on a new worksheet that will show staff that are due for retraining. The period is 2 years but we have a 2 month leeway before retraining needs to occur.

    I have rpoduced a report with the staff names but cannot for any money get the due dates to copy into the report. The data used to determine if training is required is all in the same row but may be in a different column. This will be the last minus 1.

    I have experimented with this "If Range("c20").End(xlToLeft).Offset(0, -1) >= Date - 60 Then" but when I try to copy this I keep getting an error message.

    Help
    cheers

    Phil Carter

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

    Re: Copy conditional date (xppro mso2003)

    Your expression is VBA code, so I'm not sure what you mean by 'copy' - it's not a formula. Could you post a sample workbook?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy conditional date (xppro mso2003)

    Hans hi

    I should have mentioned that I am trying to create a macro to produce a report that shows staff that are due for retraining which will be + or - minus 2 months from the current date. See attached.

    As you will see I have been playing around with the code (I think it is code that you provided for another purpose!).

    cheers
    cheers

    Phil Carter

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

    Re: Copy conditional date (xppro mso2003)

    I'm very sorry, but I don't understand what you're trying to do. Could you try to explain it in simple terms, preferably using an example from the workbook you attached?

    PS Your code refers to a worksheet named Report, but there is no such sheet in the workbook. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy conditional date (xppro mso2003)

    Hans hi

    If look at the worksheet for myself "Phil Carter" you will see that in row C20 I have several dates, the last of which is 08/02/2006 (Kiwi date dd/mm/yyyy). The report I want produce is one that checks this "last" date entry for all worksheets and if it is + or _ 2 months from the date the report is generated them copy the staff members name and the due date (2 years after the last date entered) to the worksheet named "Report".

    The code I am developing is in "Module 1" and is the "Sub FirstAidTrainingRpt()".

    This code at the start checks for a worksheet named "Report" if it exists, deletes it and then creates a new one named"Report":
    Set oWSAct = ActiveSheet
    On Error Resume Next
    Set oWSRpt = Worksheets("Report")
    On Error GoTo 0
    Application.DisplayAlerts = False
    If Not oWSRpt Is Nothing Then oWSRpt.Delete
    Set oWSRpt = Nothing
    Set oWSRpt = Worksheets.Add(After:=Worksheets(Worksheets.Count) )
    oWSRpt.Name = "Report"

    I thank you very much for your time and I hope this clarifies what I am trying to do.

    cheers
    cheers

    Phil Carter

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

    Re: Copy conditional date (xppro mso2003)

    Does the attached version do what you want? Or should the date in the report be 2 years after the "last date"? If so, change the line

    rng.Copy Destination:=oWSRpt.Range("C" & (I + 1))

    to

    oWSRpt.Range("C" & (I + 1)) = DateAdd("yyyy", 2, rng)

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy conditional date (xppro mso2003)

    Hans

    That works, thanks very much. Just having a few issues with the period(Date >=730 (2 years) And Date <=??) I'll work through this thnaks again
    cheers

    Phil Carter

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

    Re: Copy conditional date (xppro mso2003)

    What exactly do you want to calculate?

  9. #9
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy conditional date (xppro mso2003)

    Hans

    As you will have seen in the worksheet I record the date of the training. The refresher courses are every 2 years therefore I need to produce a report that gives me the names of staff that are due (or overdue) for retraining at 22 months (670 days) but I do not want those that have lapsed (more than 21/2 years, 1100 days).
    cheers

    Phil Carter

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

    Re: Copy conditional date (xppro mso2003)

    Try

    If rng >= DateAdd("m", -30, Date) And rng <= DateAdd("m", -22, Date) Then

    This says: if the date in rng is at most 30 months ago but not less than 22 months ago, then ...

  11. #11
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Re: Copy conditional date (xppro mso2003)

    Hans that's exactly what I was after.

    Many thanks once again
    cheers

    Phil Carter

Posting Permissions

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