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

 

[vbaNumpty]

​

So search for this value rather than a text …​

​

As it works manually so redo it just activating before the Macro Recorder in order to compare the parameters used …​

I have tried with the number value of the date, and it does not work either through the code or manually.

 

[Marc L]

​

As the Excel basics like here its Find feature - VBA help must read ! - always works when the Logic is respected​

so compare the parameters with the Macro Recorder …​

 

[vbaNumpty]

I have even now tried the following after recording a new macro that worked while I was manually doing the steps, but fails when trying to run the VERY CODE that was made doing those steps manually. At a complete loss right now.

Sub Macro4()
'
' Macro4 Macro
'

'
    Application.FindFormat.NumberFormat = "d-mmm-yy"
    Cells.Find(What:="06-May-21", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Activate
    Cells.FindNext(After:=ActiveCell).Activate
End Sub

 

[Joe4]

Your format and data do NOT match.

"06-May-21" would have a Custom format of "dd-mmm-yy", not "d-mmm-yy".
It does make a difference to your code.

Out of curiosity, where are you located?
European version of Excel VBA and American version of Excel VBA handle dates a little differently.
So I was wondering if that may be why I cannot recreate your issues.

 

[Marc L]

​

But you still search a text rather than a date … And of course just follow the VBA help example this error can not raise …​

​

To avoid a NES according to this obvious point link your workbook with a files host website …​

​

(NES : Never Ending Story …)​

 

[vbaNumpty]

Your format and data do NOT match.

"06-May-21" would have a Custom format of "dd-mmm-yy", not "d-mmm-yy".
It does make a difference to your code.

Out of curiosity, where are you located?
European version of Excel VBA and American version of Excel VBA handle dates a little differently.
So I was wondering if that may be why I cannot recreate your issues.

I added a the second d to the format and I get a run time error 1004 in doing so.

I just dont understand why it works doing the manual use of the find me, but when I record the macro and don't change a single thing in the code it errors out. Isn't it recording what I did successfully?

 

[Marc L]

​

It's not you as the Recorder is not friendly with your 'dates' …​

​

Read post #15 …​

 

[vbaNumpty]

​

But you still search a text rather than a date … And of course just follow the VBA help example this error can not raise …​

​

To avoid a NES according to this obvious point link your workbook with a files host website …​

​

(NES : Never Ending Story …)​

I have tried to let it search the date, I guess I am not understanding exactly what you have been trying to convey. Here is a link to a google drive:

MacroFile1.xlsm

drive.google.com

The code in question is in Module 3 and I was testing it on the Orders Sheet

 

[Marc L]

​

Search which date on which worksheet ?​

 

[vbaNumpty]

​

Search which date on which worksheet ?​

I was using "06-May-21" as can be seen in the code, it will be replaced with a variable in the future that is user selected, and it is searching the range A4:L30 on the Orders sheet (last one)