Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching for Month (97 SR2)

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Month (97 SR2)

    =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
    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Month (97 SR2)

    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!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Searching for Month (97 SR2)

    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(xlCellTypeCons tants)
    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching for Month (97 SR2)

    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
    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •