Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date ranges in VBA
All
Have borrowed some really useful code for calculating the number of emails in a specific mail folder... However, the code uses a single date - I would like to use a range identified in two cells eg Date one - Sheet 1 A1 and Date two Sheet 1 A2 Below is the code and I am at a loss as to how to update this Dim iCount As Integer, DateCount As Integer Dim myDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myDate = Sheets("Sheet1").Range("A1").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1 End With Next iCount any assistance would be greatly appreciated Shaz |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date ranges in VBA
Try it this way. I broke up your Dim statement and added one to define the
EmailCount (don't use it if it was defined earlier). I changed MyDate to MyFirstDate and also added variable MyLastDate. The test will test and count dates that are within the range, inclusive of the first and last dates. Dim iCount As Integer Dim DateCount As Integer Dim EmailCount As Long ' if not defined earlier Dim myFirstDate As Date Dim myLastDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myFirstDate = Sheets("Sheet1").Range("A1").Value myLastDate = Sheets("Sheet1").Range("A2").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) = myFirstDate And _ DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) <= myLastDate Then DateCount = DateCount + 1 End If End With Next iCount "shaz0503" wrote: All Have borrowed some really useful code for calculating the number of emails in a specific mail folder... However, the code uses a single date - I would like to use a range identified in two cells eg Date one - Sheet 1 A1 and Date two Sheet 1 A2 Below is the code and I am at a loss as to how to update this Dim iCount As Integer, DateCount As Integer Dim myDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myDate = Sheets("Sheet1").Range("A1").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1 End With Next iCount any assistance would be greatly appreciated Shaz |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Date ranges in VBA
JLatham - you are a legend - will play with this and will hopefully save many
hours of manually counting emails for a Business Unit. Shaz "JLatham" wrote: Try it this way. I broke up your Dim statement and added one to define the EmailCount (don't use it if it was defined earlier). I changed MyDate to MyFirstDate and also added variable MyLastDate. The test will test and count dates that are within the range, inclusive of the first and last dates. Dim iCount As Integer Dim DateCount As Integer Dim EmailCount As Long ' if not defined earlier Dim myFirstDate As Date Dim myLastDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myFirstDate = Sheets("Sheet1").Range("A1").Value myLastDate = Sheets("Sheet1").Range("A2").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) = myFirstDate And _ DateSerial(Year(.ReceivedTime), _ Month(.ReceivedTime), _ Day(.ReceivedTime)) <= myLastDate Then DateCount = DateCount + 1 End If End With Next iCount "shaz0503" wrote: All Have borrowed some really useful code for calculating the number of emails in a specific mail folder... However, the code uses a single date - I would like to use a range identified in two cells eg Date one - Sheet 1 A1 and Date two Sheet 1 A2 Below is the code and I am at a loss as to how to update this Dim iCount As Integer, DateCount As Integer Dim myDate As Date EmailCount = objFolder.Items.Count DateCount = 0 myDate = Sheets("Sheet1").Range("A1").Value For iCount = 1 To EmailCount With objFolder.Items(iCount) If DateSerial(Year(.ReceivedTime), Month(.ReceivedTime), Day(.ReceivedTime)) = myDate Then DateCount = DateCount + 1 End With Next iCount any assistance would be greatly appreciated Shaz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Ranges | Excel Discussion (Misc queries) | |||
Using IF and date ranges | Excel Worksheet Functions | |||
date ranges | Excel Worksheet Functions | |||
compare date to various date ranges and sum value | Excel Worksheet Functions | |||
If then Date Ranges | Excel Worksheet Functions |