Using For...Each...Next with a selection of rows

[Guest]

I have a spreadsheet that has a couple thousand lines of data. The first
column is a date field. I want to programmatically get a count of how many
lines there are for each day in a specified month. Not being very familiar
with the Excel object model, I need a little help. Logically, this is what I
want to do:

dim myArray(31) 'number of possible days in a month
dim myDay, myMonth as Integer

1) Select the entire region of data
2) For each row in the selection
3) if the month in the date column is equal to myMonth, then set myDay
variable to be the day in that date column field
4) increment myArray(myDay-1) by 1 '-1 is to accomodate 0-based array index

Can anyone help me get a start on this? Thanks!

 

[Ronald Dodge]

If Column A in your "Sales" worksheet is your date column, then try the
following:

Assuming row 5 is the first data row, and the number of rows can vary.
Assuming reporting month is July.

-------------Start Of Code------------

Option Explicit
Option Base 0

Sub CountDays()
Dim lngarrDay(30) as Long 'Note, this is with elements 0 to 30, thus
still 31 elements.
Dim rngDate as Excel.Range, lngDateCol as Long, lngCurRow as Long,
wshSales as Excel.Worksheet
Dim lngReportMonth as Long

Set wshSales = Thisworkbook.Worksheets("Sales")
lngDateCol = 1
lngReportMonth = 7
For lngCurRow = 5 to wshSales.Range("A65536").End(xlUp).row Step 1
If VBA.Month(wshSales.Cells(lngCurRow,lngDateCol).Value) =
lngReportMonth Then
lngArrDay(VBA.Day(wshSales.Cells(lngCurRow,lngDateCol).Value -
1) = _
lngArrDay(VBA.Day(wshSales.Cells(lngCurRow,lngDateCol).Value -
1) + 1
End If
Next lngCurRow
End Sub

--------------End Of Code-------------

Note, all elements in the array are initialized to a 0 value via the "Long"
data type declaration.

--

Sincerely,

Ronald R. Dodge, Jr.
Master MOUS 2000

 

[squenson via OfficeKB.com]

This routine fills in the array myArray according to my understanding of your
request. As such, it is not very useful, as as soon as the macro is completed,
the content of the array is gone, so you may want to copy the values
somewhere in the sheet.

If I have misunderstood you, please let me know!


Sub CountPerMonth()

Dim i As Long
Dim myArray(31) As Long
Dim myDay As Long
Dim myMonth As Long
Dim myRange As Excel.Range

' Initialize
' Adapt the 2 lines below to your needs
Set myRange = Range("A1:X3000")
myMonth = 1


For i = 1 To myRange.Rows.Count
If Month(myRange.Cells(i, 1)) = myMonth Then
myDay = Day(myRange.Cells(i, 1))
myArray(myDay - 1) = myArray(myDay - 1) + 1
End If
Next i

' Write here what you want to do with myArray

End Sub