Thanks, Carim, Yes, this will suffice. I was hoping you would come to my rescue. :cheers:
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.
-
Acknowledged. :ole:
George
-
Thank you both, Carim and KjBox. You have both, in a well natured, and kind way, shown me my ignorance.
I just hope AcctgContoller comes back to benefit from this in interchange. She now has a choice of solutions. :congrats:
Regards...George
-
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 -
Thanks, Karim. I am a 71 year old retired former factory worker. Everything I do know in MS Excel and other computer usage is self taught. I am always open to learn more.
Regards..George
-
Yes, the Table did not paste correctly. I did not recognise it as being an Array.
The updated file I added does work.
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
[tr]
[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]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66"] [/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66"] [/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66"] [/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66"] [/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66"] [/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: right"]1[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[tr]
[td][/td]
[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]
[/tr]
[TD="class: xl66, align: center"]FALSE[/TD]
[/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