VBA - Search for and select cell containing dates

[jarrah]

Hello,

I have a range of cells (C31:G31) where one cell for example displays as "Feb 13" yet the cell value shows as "01/02/2013".

Given a variable that says "Feb", how do I search for and select the February cell within that range given that the value is 01/02/2013 please? I also need to bear in mind that the date value could change depending on the region in which this spreadsheet will be used in, so for example it needs to cope with mm/dd/yyyy as well as dd/mm/yyyy.

My ultimate aim is to sum the 4 cells below the "Feb" cell.

Many thanks in advance!

 

[AlphaFrog]

Maybe something like this...

[COLOR=darkblue]Sub[/COLOR] Find_February()
    [COLOR=darkblue]Dim[/COLOR] cell [COLOR=darkblue]As[/COLOR] Range, FebTotal [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Single[/COLOR], strMonth [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    strMonth = "Feb"
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] cell [COLOR=darkblue]In[/COLOR] Range("C31:G31")
        [COLOR=darkblue]If[/COLOR] IsDate(cell) [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Format(cell.Value2, "mmm") = strMonth [COLOR=darkblue]Then[/COLOR]
                FebTotal = Application.WorksheetFunction.Sum(cell.Offset(1).Resize(4))
                MsgBox FebTotal, vbInformation, "February Total"
                [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]For[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]Next[/COLOR] cell
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Note: this would only work for English unless you changed "Feb" in the variable strMonth to the local language.

 

[jarrah]

Hi AlphaFrog,

Thank you for your help, that did the trick nicely!!