Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using For...Each...Next with a selection of rows
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using For...Each...Next with a selection of rows
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).Val ue) = lngReportMonth Then lngArrDay(VBA.Day(wshSales.Cells(lngCurRow,lngDate Col).Value - 1) = _ lngArrDay(VBA.Day(wshSales.Cells(lngCurRow,lngDate Col).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 "ybazizi" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using For...Each...Next with a selection of rows
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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200707/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping a selection of rows | Excel Discussion (Misc queries) | |||
Printing a selection of rows | Excel Programming | |||
Object Type of a selection... counting rows in a selection | Excel Programming | |||
getting the column and rows from a selection | Excel Programming | |||
Selection of Rows | Excel Programming |