VBA - Search for and select cell containing dates

jarrah

New Member
Joined
May 1, 2013
Messages
28
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!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
Maybe something like this...

Code:
[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.
 
Last edited:

jarrah

New Member
Joined
May 1, 2013
Messages
28
Hi AlphaFrog,

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

Watch MrExcel Video

Forum statistics

Threads
1,123,331
Messages
5,601,002
Members
414,419
Latest member
JRDunya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top