MrExcel Publishing
Your One Stop for Excel Tips & Solutions

EOMONTH & DateAdd


Posted by suz on August 16, 2001 12:23 PM

I am (still) trying to get my macro up & running. I need to compile data on Jobs quoted monthly for 6 months, then filter the data 3 ways for Jobs accepted for each month and the months following. Example, Jobs quoted in February, then Jobs accepted in Feb., then March, April, etc. Everything is working great except for my dates. I need to find a way to filter for Jobs quoted between the first and last of each month. EOMONTH doesn't seem to want to work in a macro, so I tried using variables to reference my first set of start & end dates, increasing by 1 month with DateAdd. Unfortunately, that appears to add 30 days instead of a whole month. Here's my code. (Still some tweaking to do once I get dates fixed.)

'paste dates and starting values
Sheets("Summary").Select
Range("c1:J1").Select
Selection.Copy
Range("c2:J2").Select
Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
False, Transpose:=False

'set main date and main loop counter
Dim r As Range 'row counter
Set r = Worksheets("summary").Range("i2")
Dim ms1, me1 As Range 'main start & end dates
Set ms1 = Worksheets("summary").Range("c2")
Set me1 = Worksheets("summary").Range("d2")
Dim c1 As Integer
For c1 = 1 To 5 Step 1

'start loop with main dates
Sheets("table").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:=">=ms1", Operator:=xlAnd, _
Criteria2:="<=me1"

'start sub loop with secondary dates
Dim ms2, me2 As Range
Set ms2 = Worksheets("SUMMARY").Range("e2")
Set me2 = Worksheets("summary").Range("f2")

'set sub loop counter
Dim c As Integer

'set repeat sub loop counter
Dim c2 As Range
Set c2 = Worksheets("summary").Range("h2")
For c = 1 To c2 Step 1

'gear yes, lighting no
Selection.AutoFilter Field:=13, Criteria1:="true"
Selection.AutoFilter Field:=15, Criteria1:="false"
Selection.AutoFilter Field:=35, Criteria1:=">=ms2", Operator:=xlAnd, _
Criteria2:="<=me2"

'copy totals
Sheets("sbttl").Select
Range("d1:g1").Select
Selection.Copy
Range("d3").Select
ActiveCell.Offset(r, 0).Activate
Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
False, Transpose:=False

'gear yes, lighting yes
Sheets("table").Select
Selection.AutoFilter Field:=15, Criteria1:="true"
Selection.AutoFilter Field:=36, Criteria1:=">=ms2", Operator:=xlAnd, Criteria2:=">=me2"

'add 1 row to activecell offset
r = r + 1

'copy totals
Sheets("sbttl").Select
Range("d1:g1").Select
Selection.Copy
Range("d3").Select
ActiveCell.Offset(r, 0).Activate
Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
False, Transpose:=False

'gear no, lighting yes
Sheets("table").Select
Selection.AutoFilter Field:=13, Criteria1:="false"
Selection.AutoFilter Field:=35, Criteria1:="all"

'add 1 row to activecell offset
r = r + 1

'copy totals
Sheets("sbttl").Select
Range("d1:g1").Select
Selection.Copy
Range("d3").Select
ActiveCell.Offset(r, 0).Activate
Selection.PasteSpecial Paste:=xlValues, operation:=xlNone, skipblanks:= _
False, Transpose:=False

'reset fitltered fields
Sheets("table").Select
Selection.AutoFilter Field:=13, Criteria1:="all"
Selection.AutoFilter Field:=15, Criteria1:="all"
Selection.AutoFilter Field:=36, Criteria1:="all"

'add 1 row to activecell offset
r = r + 1

'reset dates for sub loop
ms2 = DateAdd("m", 1, ms2)
me2 = DateAdd("m", 1, me2)

Next c

'reset counter and dates for main loop
c2 = c2 - 1
ms1 = DateAdd("m", 1, ms1)
me1 = DateAdd("m", 1, me1)
Next c1
Sheets("TABLE").Select
Selection.AutoFilter
End Sub


Posted by Neale Blackwood on August 16, 2001 11:34 PM

There is an Excel function EDATE which may help. It adds a number of months to a date eg 31/1/01 is incremented to 28/2/01 if 1 is added. A problem will arise if you them add 1 to 28/2/01 which will give you 28/3/01. You need to add 2 to the original date of 31/1/01 to get to 31/3/01.

HTH

Neale

Posted by suz on August 17, 2001 7:10 AM

Actually another solution came to me at 3AM! The DateAdd seems to work fine for the first of each month, so I changed the filter on my data to <=First of Month and >First of Next Month. That seems to work great.

Thanks for your help.
Suz