Why is this a runtime 91 error?

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
It means it cannot find the value you are looking for.

Are you sure that your date exists, is formatted exactly like that, and does not have a time component?
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
It means it cannot find the value you are looking for.

Are you sure that your date exists, is formatted exactly like that, and does not have a time component?
Yes, I am able to successfully find the value when I use the find function manually.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
606
Office Version
  1. 2010
Platform
  1. Windows
Try to find the date value rather than a text …​
Before to select anything you must check if the object exists like yet demonstrated in the Range.Find VBA help example …​
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Try to find the date value rather than a text …​
I am not confident I follow entirely, but I tried the number value of the date and it did not work.

The formatting for the cells with dates in them are custom, I don't know if that is causing the issue.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
What cell is the value found in?
If you temporarily change the format of the cell to "General", what does it show, exactly?
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

What cell is the value found in?
If you temporarily change the format of the cell to "General", what does it show, exactly?
Cell value is in B4, if I change the format to general the value shown is: 44322
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
606
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,023
Office Version
  1. 365
Platform
  1. Windows
I put that value in cell B4 with the Custom Format of "dd-mmm-yy", and it worked for me.

Are you sure that you are on the correct sheet when you run the code?
What is the name of the module the VBA code is located in?
 

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
65
Office Version
  1. 365
Platform
  1. Windows
I put that value in cell B4 with the Custom Format of "dd-mmm-yy", and it worked for me.

Are you sure that you are on the correct sheet when you run the code?
What is the name of the module the VBA code is located in?

Yes, I am on the proper sheet and it is Module 3
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

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