Find yyyymm in date column

[richjirak]

How would I format the WHAT parameter in a FIND when I am trying to find a cell for a particular month and year
An equivalent SQL where clause would be
WHERE TO_CHAR(date_recvd,'YYYYMM') = '201311'

I can do it with a loop but I want to know if I can do it with the find method+

[Leith Ross]

Hello richjirak,

This UDF will find one or more cells matching the date format and date text and return the cells' addresses separated by commas. If no matches are found an empty string is returned. If the date format is not found on the worksheet then a #VALUE error will be returned.



Function FindDate(ByVal DateFormat As String, ByVal DateText As String)

Dim Cell As Range
Dim FirstCell As Range
Dim Rng As Range
Dim Text As String

' Return Values:
' Returns the addresses of cells that match the date format and text.
' If no matches are found then an empty string is returned.
' If the date format does not exist on the worksheet then #VALUE error is returned.

Set Rng = ActiveSheet.UsedRange

With Application.FindFormat
.Clear
.NumberFormat = DateFormat
End With

Set Cell = Rng.Find("*", , xlValues, xlWhole, xlByRows, xlNext, False, False, True)

If Not Cell Is Nothing Then
Set FirstCell = Cell

Do
If Cell.Text = DateText Then
Text = Text & Cell.Address & ","
End If
DoEvents
Set Cell = Rng.Find("*", Cell)
If Cell.Address = FirstCell.Address Then Exit Do
Loop
End If

If Text <> "" Then Text = Left(Text, Len(Text) - 1)

FindDate = Text

End Function



Example
You can use this as a worksheet function to return the addresses of all cells whose dates are formated as "yyyymm" and are displayed as "201311".
=FindDate("yyyymm", "201311")

[SamT]

Cell A1 = 4/14/2016 21:49 (Now function)

Sub t()
Set X = Range("A1:A2").Find(What:="14/2016", LookAt:=xlPart, LookIn:=xlValues)
Y = X.Address
End Sub