none
Excel Macro to Find Multiple Dates RRS feed

  • Question

  • I'm trying to make a macro that finds when a calibration date is coming up in the next 2 weeks.  This code works, but only for one date:

    Set rnValue = .Find(What:=(Date + 10))


    This find any calibrations due 10 days from now.

     

    Thanks

    Thursday, October 13, 2011 5:13 AM

All replies

  • You wouldn't use the Find function but there are all sorts of other ways. Eg a helper column with
    =TODAY() >(A1-10)
    or maybe
    =TODAY() >=(A1-10)

    You could use a filter maybe use a filter with "is less than" (or equal to)

    Loop cells with code and falg them, maybe with colour or in an adjacent cell.

    Other ways too depending on your scenario

    Peter Thornton

    Thursday, October 13, 2011 8:25 AM
    Moderator
  • Hi

    May I recommand using Conditional Formatting.

    You would highlight starting from the top, the column you have the dates, and this formula would color the cells

     from today to up to 10 days up.It adjust itself everyday and is very visual.

    =AND(A1>TODAY(),A1<TODAY()+11)  Change the cell reference to your needs.

    HTH

    Cimjet


    Cimjet
    Friday, October 14, 2011 12:41 PM
  • On Thu, 13 Oct 2011 05:13:26 +0000, mat10000 wrote:
     
    >
    >
    >I'm trying to make a macro that finds when a calibration date is coming up in the next 2 weeks.  This code works, but only for one date:
    >
    >Set rnValue = .Find(What:=(Date + 10))
    >
    >
    >This find any calibrations due 10 days from now.
    >
    >
    >Thanks
     
    What do mean by "finds"?  What should the macro do with this information?
     
    If you just want to mark those dates on a table, then use conditional formatting.
     
    If you want to display a list, then something like the routine below can filter your list and copy it to another location.  You'll need to modify this for the specifics of your data.
     
    ===========================
    Option Explicit
    Sub CalibDates()
        Dim MinDate As Date, MaxDate As Date
        Dim DataTbl As Range
        Dim rg As Range, res As Range
    MinDate = Date
    MaxDate = Date + 10
    Set DataTbl = Range("A1").CurrentRegion 'or some other way to define this
    Set res = Range("F1") 'range where to put results.  Could be on a different sheet
     
    Application.ScreenUpdating = False
    'Field argument below is whatever column of the table contains the
    ' calibration date
    DataTbl.AutoFilter field:=2, Criteria1:=">=" & MinDate, _
        Operator:=xlAnd, Criteria2:="<=" & MaxDate
    Set rg = DataTbl.SpecialCells(xlCellTypeVisible)
    res = rg.Copy(Destination:=res)
     
    DataTbl.AutoFilter
     
    Application.ScreenUpdating = True
     
    End Sub
    ===========================
     
    If you want something else, you'll need to be more specific.
     

    Ron
    Friday, October 14, 2011 2:17 PM
  • Here is the rest of the code:

     

     

    Sub Macro1() 

    ' 

    ' Macro1 Macro 

    ' Macro recorded 10/12/2011 by User 

    '

    Option Explicit

     

    Sub Check_Date_Send_Mail()

    Dim wbBook As Workbook

    Dim wsSheet As Worksheet

    Dim rnDate As Range, rnValue As Range

    Dim stAddress As String, stMsg As String

    Dim stRecipient As String, stSubject As String

    Dim stPost As String

     

    Set wbBook = ThisWorkbook

    Set wsSheet = wbBook.Worksheets("Sheet1")

     

    With wsSheet

    Set rnDate = .Range("C:C")

    End With

     

    stMsg = "------------------------------------------------------------------" & Chr(11) & " ID#             DUE DATE             LOCATION" & Chr(11) & "------------------------------------------------------------------" & Chr(11)

     

    '''''''''''''''''''''

     

    With rnDate

    Set rnValue = .Find(What:=(Date + 10))

    If Not rnValue Is Nothing Then

    stAddress = rnValue.Address

    Do

    With rnValue

    stMsg = stMsg & " " & .Offset(0, -2).Value & "             " & .Offset(0, 0).Value & "                   " & .Offset(0, 1).Value & Chr(11)

    End With

    Set rnValue = .FindNext(rnValue)

    Loop While Not rnValue Is Nothing And rnValue.Address <> stAddress

    Else

    MsgBox "No renewals this week...", vbInformation

    Exit Sub

    End If

    End With

     

     

    '''''''''''''''''

     

    stRecipient = "me@me.com"

    stSubject = "Calibrations Due"

     

    stPost = "mailto:" & stRecipient & "?"

    stPost = stPost & "subject=" & stSubject & "&"

    stPost = stPost & "body=" & stMsg

     

    ActiveWorkbook.FollowHyperlink (stPost)

     

    '

    End Sub

     

     

    So right now it creates an email to me@me.com with the relevant information if there are any calibrations exactly 10 days from today, but i need it to be anytime within two weeks. I found this on another site, its far beyond my knowledge. Thank you

    • Edited by mat10000 Wednesday, October 19, 2011 1:50 AM
    Wednesday, October 19, 2011 1:44 AM
  • On Wed, 19 Oct 2011 01:44:00 +0000, mat10000 wrote:
     
    >So right now it creates an email to me@me.com with the relevant information if there are any calibrations exactly 10 days from today, but i need it to be anytime within two weeks. I found this on another site, its far beyond my knowledge. Thank you
     
    I would suggest modifying the routine I provided to email to you the filtered results.  You could do some formatting on the lines if it is not exactly how you like it.
     
    You should change the location of the results to be a different worksheet, and then just email that sheet to yourself; or you could just copy it to the clipboard, and mail the copied data to yourself.
     
    You could modify your present method to loop through each day, one at a time, and generate the data.  It would require some rewriting since, right now, the routine exits if nothing is found on a particular day.  The looping method would be slower than the autofilter method.
     
    I don't have time to work on either tonight.  But consider the options.
     
     

    Ron
    Wednesday, October 19, 2011 2:50 AM
  • I like having it generate an email because it won't just go to me, the email would need to go to about 20 people. Would one of these work?

     

    Set rnValue = .Find(What:=(Date + (<=14))

    Set rnValue = .Find(What:=(Date + 1) or (Date +2) or (Date +3) or (Date +4) or (Date +5) or (Date +6))

    Or change the ELSE statement and write 14 macros with Date +1 thru Date + 14 with one more macro to run them all.

     

    Thanks again

    • Edited by mat10000 Wednesday, October 26, 2011 6:09 AM
    Wednesday, October 26, 2011 6:06 AM
  • On Wed, 26 Oct 2011 06:06:09 +0000, mat10000 wrote:
     
    >
    >
    >I like having it generate an email because it won't just go to me, the email would need to go to about 20 people. Would one of these work?
    >
    >
    >Set rnValue = .Find(What:=(Date + (<=14))
    >
    >Set rnValue = .Find(What:=(Date + 1) or (Date +2) or (Date +3) or (Date +4) or (Date +5) or (Date +6))
    >
    >Or change the ELSE statement and write 14 macros with Date +1 thru Date + 14 with one more macro to run them all.
    >
    >
    >Thanks again
     
    That is a question you can answer easily yourself by trying them out.
     
    As I wrote before, I would recommend building a filtered list, as I showed you, and then mailing that filtered list (with formatting if necessary).  I provided the code to filter the list.  Is there a problem with that approach?
     
    You could certainly execute 14 macros, or however many you need to, but that seems less efficient.
     

    Ron
    Wednesday, October 26, 2011 9:59 AM
  • Hello,

    I  am familiar with excel but very new to VBA.  I have a calendar i am taking a stab at for my mgr at work.  I am figuring out most of the VBA as i go along.  But here is where i am stuck.

    so in the excel part, she wants to click on the calendar dates and have it spit out multiple dates in the report.

    eg.

    seminar on jan 1-3, 2016

    instead of her having to do it separate times, how can she do it once where the report shows jan 1-3 instead of

    jan 1

    jan 2

    jan 3

    do you need to see a code of what i have?

    Thanks

    Friday, November 20, 2015 7:54 PM
  • Instead of adding a new question on a four year old thread; I would suggest you start a new thread, and clearly state all of your requirements in that question, much more clearly than you have here.

    Ron

    Saturday, November 21, 2015 11:04 AM