Posts by George-1947

    How to determine if the date shown is before January 1 of current year in an If formula to determine whether arrears are payable. I need to know if the date difference is more than the days between Today() and January 1, 2019


    where if (datedif is more than today minus January 1, 2019, 0,arrears payable. I can use multiple columns to work this out but I am asking if there is a concise formula for this task, please.


    =if(datedif(D3,-today()<days to start of year,0,"Arrears to pay") where D3 = January 1, 2019.

    Yes, she went quiet all of a sudden.
    Carim, I have been thinking about your solution. While it certainly does look impressive it does have a drawback that my rather crude solution does not have. You have locked it to 26 rows. If the Acctg Controller increases her number if rows your solution will not function whereas with my solution she need only to increase the =Sum(xx:xxxx) to whatever range is required.


    George

    Thanks Carim. Nesting loops in VBA is beyond me. I usually hunt on line for something readily adapted, as in my example where I altered the output from hard printing to email. This is why I requested online help.
    While I have a mental picture of what needs to be done I don't know how to do it.
    Regards...George

    A slightly improved fix: =IF(INT(C2)>INT(B2),IF(COUNTIF($A$2:$A2,$A2)=1,1,"")) but you are still left with "FALSE" on the items ordered and shipped on the same day. Maybe somebody else can add to my work and create a fix for you.


    Regards...George
    [TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 282"]

    [tr]


    [TD="width: 69"]Order Nbr[/TD]
    [TD="width: 75"]Order Date[/TD]
    [TD="width: 104"]Ship Date[/TD]
    [TD="width: 64"] [/TD]
    [TD="class: xl66, width: 64"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647844[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:21[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647844[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:21[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648106[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:03[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648067[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:01[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648028[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 9:59[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648003[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:06[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1648003[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:06[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647930[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:14[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647919[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:23[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647919[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:23[/TD]

    [td][/td]


    [TD="class: xl66"] [/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647830[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:18[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647815[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/04/2019 10:11[/TD]

    [td][/td]


    [TD="class: xl66, align: right"]1[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647670[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:18[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647669[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:20[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647655[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:21[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647624[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:24[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647624[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:24[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647593[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:25[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647575[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:17[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647573[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:27[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647566[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:29[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647537[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:30[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="class: xl63, width: 69, align: right"]1647498[/TD]
    [TD="class: xl64, width: 75"]3/01/2019[/TD]
    [TD="class: xl65, width: 104"]3/01/2019 15:28[/TD]

    [td][/td]


    [TD="class: xl66, align: center"]FALSE[/TD]

    [/tr]


    [/TABLE]

    Try this in Column "E" : it will place the word, "Distinct" if the result is Distinct. On the rows with the same date where you now get "#VALUE" you will now have, "FALSE". You can use COUNTIF to tally up the "Distinct. I hope this helps you. I did not try COUNTIF but if you need a number you you can use this formula: =IF(E2="Distinct",1,"") and copy that formula down your page. That will leave cells marked "FALSE" as blank so COUNTIF will work.


    =IF(INT(C15)>INT(B15),IF(COUNTIF($A$2:$A15,$A15)=1,"Distinct",""))


    Regards...George

    Excel VBA code to write Print Area as PDF and email
    Hello, I have created a excel workbook that uses =Indirect(“Data!A &RowIndex”) to pull data through into my news letter layout.

    In my news letter I have:
    Member First Name in cell (Data!A1 &RowIndex)
    Member Last Name in cell (Data!B1 &RowIndex)
    Arrears in cell (Data!C1 &RowIndex)
    Dues in cell (Data!D1 &RowIndex)
    Total in cell (Data!E1 &RowIndex)
    Email address in cell (Data!F1 &RowIndex)
    The email body in Form cells B2:I48
    the email subject is “Monthly Meeting”

    I have a list of members’ Names in a worksheet called "Data" in Columns A & B

    The email address is also in “Form” G5 if it is easier to extract from there.

    The formula =Indirect(“xxx &RowIndex”) updates each newsletter and my current code produces an email with Print Range embedded in the news letter. I previously adapted a routine that printed the newsletter for each member to be mailed out. It can be emailed, but manually. I am just looking to amend this code to print to PDF, and then send the emails automatically.

    It is currently locked to two outputs during the testing phase. This is shown on the “Form” sheet at cell “C5”.

    Here is my current code:

    Public Const APPNAME As String = "Sample-1"
    Option Explicit

    Sub PrintForms()
    Dim StartRow As Integer
    Dim EndRow As Integer
    Dim Msg As String
    Dim MailDest As String
    Dim i As Integer

    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    Sheets("Form").Activate
    StartRow = Range("StartRow")
    EndRow = Range("EndRow")

    If StartRow > EndRow Then
    Msg = "ERROR" & vbCrLf & "The starting row must be less than the ending row!"
    MsgBox Msg, vbCritical, APPNAME
    End If

    For i = StartRow To EndRow
    Range("RowIndex") = i
    ActiveSheet.Range("B7:I48").Select
    ActiveWorkbook.EnvelopeVisible = True
    With ActiveSheet.MailEnvelope
    '.Introduction = "This is a sample worksheet."
    .Item.to = "(email addresses here)"
    .Item.Subject = "Monthly Meeting"
    .Item.Send
    '.Item.Display
    End With
    Next i
    End Sub

    Files

    • Sample-1.xlsm

      (72.5 kB, downloaded 78 times, last: )