1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Code Help

Discussion in 'Business Applications' started by Highlander1110, Feb 20, 2013.

Thread Status:
Not open for further replies.
  1. Highlander1110

    Highlander1110 Thread Starter

    Feb 19, 2013
    I would like to know or get help with the VBA coding to do the following if possible, I have an excel sheet with file links (link in column (I) link text in column (J)) there are e-mail addresses in Column (H) and required dates in column (D).
    I would like to know if the dates can be compared (today) for out of date occurrences and dates approaching 31 days of the date in column (D) (older than (=TODAY()+31)) a possibility would be to ask for user defined input on the dates after today (31) in the example above.
    In this event the sheet could be filtered to show all columns and only those rows with the dates meeting the criteria, which would then raise an email (Outlook) to be viewed prior to sending and attach the files (column (I) or (J))
    One e-mail to one recipient but multiple files if there are any?
    From: (me)
    To: (I will add a column with the recipient’s name) let’s say (L)
    CC : (someone)
    BCC: (someone else)
    Subject: “text” + cells in column (C) relative to all the date occurrences to the recipient in column (H)

    Body text
    ((Name in Column (L))

    Text 1
    Text 2
    Text 3

    A sample file is attached with arbitrary information

    Many thanks in appreciation

    Attached Files:

  2. Highlander1110

    Highlander1110 Thread Starter

    Feb 19, 2013
    I note a few views, and can appreciate its a lengthily request, I am trying to refresh my coding as it has been nearing 20 years since doing anything.

    But I have made a start; Button calls on macro to get the date and append it with user input to advance the filter criteria, and then creates a new sheet with the date of the filter.

    Sub In_Progress()
    Dim vData
    Dim firstDate As Date, secondDate As Date
    Dim ActNm As String
    'Dim LastColumn As Long
        On Error Resume Next
            Application.DisplayAlerts = False
                vData = Application.InputBox _
                (Prompt:="Please select a single cell housing the number, " _
                & "or enter the number directly.", _
                Title:="Days from Today", Type:=1 + 8)
        On Error GoTo 0
            Application.DisplayAlerts = True
                If IsNumeric(vData) And vData <> 0 Then
                    firstDate = DateValue(Now)
                    secondDate = DateAdd("d", vData, firstDate)
                    'test date increment by user input
                    MsgBox secondDate
                With ActiveWorkbook.Sheets
                    .Add after:=Worksheets("ALL - cals")
                End With
                    ActNm = ActiveSheet.Name
                On Error Resume Next
                    ActiveSheet.Name = "due - cals " & Format(secondDate, "mm-dd-yy")
    NoName:             If Err.Number = 1004 Then ActiveSheet.Name = InputBox("Give name.")
                        If ActiveSheet.Name = ActNm Then GoTo NoName
                On Error GoTo 0
                'LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
            Exit Sub
        End If
    End Sub
    I have had a look at filtering the criteria before redirecting the output to the newly created sheet and my attempts seem to work as long as the columns before column (D) are empty otherwise the filter seems to be applied to Range (A1) and not (D1) as with turning off the autofilter.

    [FONT=Calibri][SIZE=3]Sub filter_less_then_today()[/SIZE][/FONT]
    [SIZE=3][FONT=Calibri]  Dim dDate As Date[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  Dim lDate As Long[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  Dim answer As Long[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  dDate = DateSerial(Year(Now), Month(Now), Day(Now))[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  lDate = dDate[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  If Worksheets(1).AutoFilterMode = True Then[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      answer = MsgBox("Turn autofilter off (yes)" & vbCrLf & _[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      "Filter on date until now (no)" & vbCrLf, vbYesNoCancel, "Filtering ...")[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Select Case answer[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Case 6[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]          Range("D1").AutoFilter[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Case 7[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Case 2[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      End Select[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  Else[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      answer = MsgBox("Filter on date until now (yes)" & vbCrLf & _[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      "Do nothing and leave it as it is (no)" & vbCrLf, vbYesNo, "Filtering ...")[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Select Case answer[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Case 6[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]          Range("D1").AutoFilter field:=1, Criteria1:="<" & lDate[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      Case 7[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]      End Select[/FONT][/SIZE]
    [SIZE=3][FONT=Calibri]  End If[/FONT][/SIZE]
    [FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]
    Any pointers of shortening the code or pointing me in the direction that would fix the code would be appreciated

    thank you!
  3. XCubed


    Feb 21, 2013

    I'm not exactly clear on what it is you are trying to do especially where you are prompting for user input. From what you seem to be saying is that you are trying to get a list of people who are over 31 days over the "Cal due date". Is that correct?

    Then I'm not sure why you are using the Filter. Wouldn't it be better to have the Macro test for the age and copy the results to a new worksheet?

    Here is an example of what I mean using an existing sheet for the results.

    Sub filter_less_then_today2()
      Dim dDate As Date
      dDate = Date + 31
            If Worksheets(1).AutoFilterMode = True Then
            End If
     'Find last row in All-cals
     lRow = Range("A" & Rows.Count).End(xlUp).Row
     'Start loop through each row Starting at 2 to find dates GT Today+31
     Count = 2
     For i = 2 To lRow
     If Cells(i, 4) <= dDate Then
        Range(Cells(i, 1), Cells(i, 10)).Copy
        Sheets("Due - Cals").Cells(Count, 1).PasteSpecial
        Count = Count + 1
     End If
    End Sub

    Some comments about your code (pretty good after 20 years:))

    Where you reference Workheets(1) in the example you attached this actually points to the sheet named Welding Plants which is hidden. To make your reference work unhide Welding Plants and physically move All - calls to be the first worksheet.

    To fix the problem with the filters, this line

    Range("D1").AutoFilter field:=1, Criteria1:="<" & lDate

    would need to change to

    Range("D1").AutoFilter field:=4, Criteria1:="<" & lDate

    Give this some thought and let me know how you'd like to proceed.
  4. Highlander1110

    Highlander1110 Thread Starter

    Feb 19, 2013
    Hello XCubed
    Firstly thank you for your reply!
    The task, equipment Calibration tracking (due &#8211; cals), impacting factors, using/tracking/shipping/storage all have impacts on due date (actioning calibration) for this reason we need to know what is out of calibration from today, also projections based on facility/personnel requirements (the future, +31, as an example) as it won&#8217;t only be me using the Excel sheet it; would be good to have a filtered listing to double check what will eventually be attached to the self generated e-mails.
    A macro would work great and the user could then filter the new sheet (to corroborate the e-mail attachments) or if the macro ordered the dates sequentially (in effect: found and filtered) this would be a plus; as to cutting out the manual filtering, why not just filter from the start, maybe it&#8217;s easier to code your way?
    Your coding is greatly appreciated and I think an adaption will be used to attach the hyperlinked documents to the open e-mails.
    I will look at changing my code as per your comments and let you know how it goes

    Again my appreciation for your help, I would have P.M'd you, but forum rules!

    Regards H.
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1090295

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice