Search Separate Workbook - Error

[Logit]

Please refer to the attached files.

The search function works eventually after several iterations through. The issue centers on setting the separate workbook to be opened from the main program and then being able to click on
the returned data in the Listbox for it to be displayed above .... I'm just not getting it corrected as needed.

What edits are required to get it working correctly ?

Thank you.

[NoSparks]

Can you explain and/or describe what this means, and what we are supposed to see or not see happening?

The search function works eventually after several iterations through.

[Logit]

On my machine, when a search term is entered in "Search Prefix" the query function is initiated. Rather than find the appropriate data and display in the ListBox in one motion, the project goes through several 'steps' or 'motions' to complete the query .... eventually displaying the data term in the ListBox. Another project utilizes this same method (syntax) of querying data within the same workbook (not another separate workbook) and completes the query function in a 'single step'. There is no hesitation - no obvious indication of going through several iterations of the query and then displaying the found data.

The separate workbook holding the database (FCC.xlsx) is opened when 'US Call Sign Search' is initiated (see: ThisWorkBook). The separate workbook is also referred to in the 'f_FindAll' form code.
I'm certain how those are coded and/or where that syntax resides in the project ( 'US Call Sign Search') is the crux of the matter. I'm not certain how to correct it.

I hope I've explained it more clearly ? The written word is sometimes harder to convey meaning or thoughts.

Thanks NoSparks !

[Norie]

How can we replicate the problem(s) with the attached workbooks?

[NoSparks]

Playing around with this the way I did the biggest issue was whether or not the FCC workbook was open.

With the FCC file you attached (2000 rows) things appear pretty much instantaneous.
No doubt your 'real' FCC workbook is the one with a million rows which takes an eternity to open.
I suspect the FindAll function would take substantially longer as well.

I'm looking forward to see the response to Norie.

[Logit]

Norrie and NoSparks

Sent you a private message.

[Logit]

I've gotten it to load the query find in a reasonable time but am now getting a "Select Method of Range Class Failed" error with the highlight shown below in red:

Please Login or Register  to view this content.

Any syntax changes that may have occurred from the first posted code - to this snippet - are in this snippet.

HTH

[NoSparks]

You can't make a selection on a sheet that isn't active.

Try the attached.
It's named slightly different and you'll need to adjust path to files.

[Norie]

Do you need this line?

Please Login or Register  to view this content.

PS You can exit a For loop using Exit For.

[Logit]

NoSparks & Norie

Thank you for the assist. Your changes work very well.

Norie: I removed that one line and you are correct ... it's not needed. I'll have to see if removing it from the other project has the same effect.

NoSparks:

You can't make a selection on a sheet that isn't active.

Are you referring to the sheet or the workbook ? The added line 'wb.Activate' ... does that apply to the sheet or the workbook ? I presumed the "wb." meant workbook ? I'm learning ... help me understand ?

[NoSparks]

The added line 'wb.Activate' ... does that apply to the sheet or the workbook ? I presumed the "wb." meant workbook ?

Yes it does mean workbook. That's what it's declared as and set to earlier in the sub.
Guess it's just lucky that the workbook is always on the right sheet.

[Logit]

What if you wanted to specify another worksheet, other than #1. What would the syntax look like then ?

[NoSparks]

Specify the sheet

Please Login or Register  to view this content.

Do I sense the FCC workbook having 26 sheets ?

[Logit]

No. I managed to cull it down to a single sheet, approx. 800,000 rows. However, discovered something else I didn't realize. In order to remove the Expired/Inactive/Terminated listings, there are actually
two identifiers that have to be referenced in the 'master list'. ("Master List = those id's that need to be removed). Now, I'm scratching my head how to modify the previous code that culls out those id's so it will
reference first the call, then a serial number that is assigned to the call. There are some calls that have several locations in the list. One may be terminated ... a second rendition may be expired and the third will be the present owner who is active. Each one has a different/unique serial number assigned to it.

The code that accomplished the task only refers to the call.