CODEWITHSUNDEEP
vbaexcel
Ashok
Ashok

Reputation: 394

Why FindFunction can't Find the Partial Date in the Range - VBA

Refer below code. find function not find the partial date in the range.i knew that we can use for loop to find the partial date in the range but why find function doesn't work for finding the partial date? The question is simple but i can't find the answer in google...

Sub Demo()

Dim day As Long
Dim monthYear As String
Dim ws As Range


    monthYear = Right(ThisWorkbook.ActiveSheet.Range("C10"), 7) ' monthYear = 08-2017
    Set ws = ThisWorkbook.ActiveSheet.Range("D3:D30")

    Set c = ws.Find(monthYear, Lookat:=xlPart)

End Sub

ws Range

11-01-2016
29-02-2016
28-03-2016
27-04-2016
27-05-2016
27-06-2016
29-08-2016
29-08-2016
27-09-2016
27-10-2016
28-11-2016
27-12-2016
27-01-2017
27-02-2017
27-03-2017
27-04-2017
29-05-2017
27-06-2017
13-08-2017
28-08-2017

Upvotes: 1

Views: 271

Answers (3)

Docmarti
Docmarti

Reputation: 386

It's complicated to use the Find function with XlValues in the lookin parameter to search for a string value in a dates range.

The cells numberformat property must then be a date format other than the default date format.

It is also important to ensure that the width of the columns is large enough to avoid the dates being replaced by #####.

You could use something like this : 

ThisWorkbook.ActiveSheet.Range("C10").NumberFormat = "d-m-yyyy"
t = Split(ThisWorkbook.ActiveSheet.Range("C10").Text, "-")
monthYear = t(1) & "-" & t(2)
Set ws = ThisWorkbook.ActiveSheet.Range("D3:D30")
ws.NumberFormat = "d-m-yyyy"
Set c = ws.Find(monthYear, LookIn:=xlValues, Lookat:=xlPart)

Upvotes: 0

danieltakeshi
danieltakeshi

Reputation: 939

This code transforms the Date to search in Long to make a loop through the month and then is converted back to Date. So it loops a find through a whole month (slow).

If you have a huge workbook, It is not recommended.

Sub Demo()

Dim monthYear As Date
Dim rng As Range, rng2 As Range, cellFound As Range
Dim ws As Worksheet
Dim i As Long, lastrow As Long

    Set ws = ThisWorkbook.Sheets(1)
    lastrow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row


    Set rng = ws.Range(ws.Cells(3, 4), ws.Cells(lastrow, 4))

    monthYear = (Right(ThisWorkbook.ActiveSheet.Range("C10"), 7)) ' monthYear = 08-2017
    intDaysInMonth = day(DateSerial(Year(monthYear), Month(monthYear) + 1, 0))

    For i = 0 To intDaysInMonth - 1
        LookingFor = CLng(monthYear) + i
        LookingForString = CStr(CDate(LookingFor))
        With rng
            Set cellFound = .Find(what:=LookingForString, LookIn:=xlValues, MatchCase:=False)
                If Not cellFound Is Nothing Then
                    FirstAddress = cellFound.Address
                    Do
                        Debug.Print cellFound.Address
                        Set cellFound = .FindNext(cellFound)
                    Loop While Not cellFound Is Nothing And cellFound.Address <> FirstAddress
                 End If
        End With
    Next i

End Sub

It is messy and there are better ways to optimize it. I think you were receiving error 13: Type mismatch.

Upvotes: 0

Vityata
Vityata

Reputation: 43595

Try like this:

monthYear = Right(CStr(ThisWorkbook.ActiveSheet.Range("C10")), 7)

Try to run this:

Public Sub TestMe()
    Dim rngCell as Range
    For Each rngCell In Range("D3:D30")
        If Year(rngCell) = 2017 Then
            Debug.Print rngCell.Address
        End If
    Next rngCell

End Sub

Then rebuild the logic, using the Year().

Concerning the Find(), try building something small like this one:

Public Sub TestMe()
    Dim rngRange    As Range
    Set rngRange = ActiveSheet.Cells.Find("08.2016")
    Debug.Print rngRange.Address
End Sub

It should work.

Upvotes: 0

Related Questions