Searching for Month (97 SR2)

Topic

New Reply

This may be a simple question, but how might I specify a month as criteria when performing a search in Excel? For example, I’d like to locate the first row where the date contains the month of June, how might I do this?

Thanks!

Reply | Quote

Replies

=INDEX(A2:A6,MATCH(E1,TEXT(A2:A6,”mmmm”),0))

where E1 houses a month name like “June” as criterion,

or

=INDEX(A2:A6,MATCH(E1,MONTH(A2:A6),0))

where E1 houses a month number like 6 as criterion.

These formulas must be array-entered (using control+shift+enter).

Aladin

Reply | Quote

Beautiful!

I suppose I can use this to return the first row where the first date is returned, how might I return the last row?

Is there a way I can define criteria in VBA to search for the first row containing a given month, where the cells contain standard dates? I figure I can then use xlPrevious and xlNext to find the first/last rows…

Thanks for all the help!

Reply | Quote

Drk, depends what your objective is. You can find months directly in date cells depending on format just by searching for “06/” if you’re using US mm/dd/yyyy date format and the date is entered directly (constant) rather than a formula.

Using the Find dialog is not going to directly give you the first and last rows. Here’s some code I cobbled together to get both first and last rows with a specified month. It’s also format dependent and probably needs work.:

Sub FindFLMonth()
Dim rngCell As Range
Dim varCellVal As Variant
Dim lngCellRow As Long, lngFrstM As Long, lngLstM As Long
Dim intMonth As Integer, intCellM As Integer

intMonth = Application.InputBox(“Enter Month: “, “Find First & Last Row containing Month”, , , , , , 1)

For Each rngCell In Selection.EntireColumn.SpecialCells(xlCellTypeConstants)
varCellVal = rngCell.Value
If TypeName(varCellVal) = “Date” Then
intCellM = Val(Left(varCellVal, InStr(varCellVal, “/”) – 1))
If intCellM = intMonth Then lngCellRow = rngCell.Row
If lngFrstM = 0 Then lngFrstM = lngCellRow
lngLstM = lngCellRow
End If
Next rngCell
MsgBox “First: ” & lngFrstM & vbLf & _
“Last: ” & lngLstM
End Sub

Reply | Quote

In order to get the last occurrence of a given month, use one of:

=INDEX(A2:A6,MATCH(E1,TEXT(A2:A6,”mmmm”))) where E1 is e.g., “June”,

=INDEX(A2:A6,MATCH(E1,MONTH(A2:A6))) where E1 is e.g., 6.

Both are again array-entered (using control+shift+enter).

I must leave the VBA part of your question to someone else.

Aladin

Reply | Quote