Why is this a runtime 91 error?

[vbaNumpty]

I am following a tutorial that demonstrates how to use advancedfilter and find functions in VBA code to populate a list box and allow for editing of values. Despite following along and triple checking that my code matches that of the video, when the following code is run I get a runtime 91 error while in the video tutorial the code runs fine. The error is happening on the last line of code, FindMe.Select.

Sub Macro7()
'
' Macro7 Macro

    Set FindMe = Range("A4:L30").Find(What:="06-May-21", LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
    FindMe.Select
End Sub

 

[Joe4]

As I said in my last post, the format matters!
You have formatted your dates as "y-mm-dd".
This means that there are NO leading zeroes in front of the days value!

The value in cell B4 is formatted to show "6-May-21", not "06-May-21".
If you are going to search by Custom Date format, it needs to match EXACTLY.
So, just change your code to look for "6-May-21" and it works.

 

[vbaNumpty]

As I said in my last post, the format matters!
You have formatted your dates as "y-mm-dd".
This means that there are NO leading zeroes in front of the days value!

The value in cell B4 is formatted to show "6-May-21", not "06-May-21".
If you are going to search by Custom Date format, it needs to match EXACTLY.
So, just change your code to look for "6-May-21" and it works.

Thanks, it also works for double day formats like 19-May-21 for instance. When recording the macro I had used 06-may-21 and it worked and it was copied into the code base I guess.

also when I see the b4 value it does have a leading 0 infront of it.

 

[Joe4]

Thanks, it also works for double day formats like 19-May-21 for instance. When recording the macro I had used 06-may-21 and it worked and it was copied into the code base I guess.

Yes, that is because "19" is displayed as "19" for both "d" and "dd" formats.
However, if you have a single digit like "6", it displays as "6" for "d", but as "06" for "dd".
"dd" forces a leading zero for single digit numbers, "d" does not.

 

[Marc L]

If I follow post #21 on my side it can't work 'cause on my local Excel version the post #18 workbook​

dates are displayed as jj-mmm-aa like for example 06-mai-21 (dd-mmm-yy) …​

As on a local Excel version the formatting used is not the cell NumberFormat property but the NumberFormatLocal !​

​

So whatever the Excel version and whatever the cell Date formatting the trick is to not search for a text​

but for a date combined with a specific parameter like in this demonstration according to post #18 attachment :​

Sub Demo1()
       Dim Rf As Range, D As Date
           D = #5/6/2021#
       Set Rf = Sheet8.UsedRange.Find(D, , xlFormulas, xlWhole, xlByRows, , , , False)
    If Not Rf Is Nothing Then
           Rf.Parent.Select
           Rf.Select
       Set Rf = Nothing
    End If
End Sub

 

[Marc L]

As on a local Excel version the formatting used is not the cell NumberFormat property but the NumberFormatLocal !

When the cell uses an international date format, those beginning with an '*' when displayed in the Date format list …
​