Multipage - Search function needs help 3

[DPlank]

Hi.

I've created a multipaged userform which contains comboboxes for the purposes of allowing a user to search for data that matches their needs.

I've managed to implement a lot of the required functionality, but I was hoping for a steer on how to code a search function that could make it all work.

The data to be searched is all in Excel, on a sheet that is

xlVeryHidden

. There are up to 30 search fields which can have a value to search with.

I think this needs an array. I'm not sure how best to accomplish this. Anyone out there built something similar? or just knows how best to code a search function?

Thanks in advance,
David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[JohnAcc]

Hi There:

You could try something like:

Sub SearchEngine()
Dim myrng As Range
Dim mycell As Range
Dim criteria


criteria = InputBox("Please Enter your search criteria", "Search Criteria&quot
Set myrng = Sheets("Sheet1&quot.Range("A1:C50&quot ' Your range
For Each c In myrng
If c.Value = criteria Then foundcell = foundcell & "," & c.Address
Next
If IsEmpty(foundcell) Then
MsgBox "Your search criteria did not find any matches"
Else
MsgBox criteria & " was found in the following cells: " & vbCr & _
foundcell
End If
Exit Sub
End Sub


Rgds, John

 

[VBAjedi]

There's not enough detail on the relationship between your data and your search criteria to know for sure, but it sounds like you might be able to use the advanced filter. Set up a criteria section at the head of your data, then write code to insert the search values under the correct criteria headings and execute the filter. Loop through the data rows with something like the following to return the visible rows:

For x = FirstDataRow to LastDataRow
If Worksheets("MyWorksheet").Rows(x & ":" & x).EntireRow.Hidden = False Then
' Return to your form, or?
End If
Next x

There's probably other approaches out there, but for multiple search criteria this may be your best bet. . .

Let me know if this helps you or if you need more details!



VBAjedi

 

[DaleWatson123321]

David,

I agree with VBAjedi that the Advanced Filter is a good option. It's particulary POWERFUL when MULTIPLE fields are required in the criteria.

Because you mention the data is on a sheet that is xlVeryHidden, it appears you want to be able to extract the data (that matches the fields/criteria chosen) to a SEPARATE sheet. Advanced Filter VBA code is ideal for extracting a copy of the data to a SEPARATE sheet.

The Advanced Filter is "SUPER FAST" because it's a custom (internal) component written in the same 'C' language as Excel.

Because Microsoft has provided "less than adequate" documentation on the use of Advanced Filter, you could easily encounter difficulty in setting up the criteria.

If you become frustrated, feel free to email a copy of your file. I'll then modify and return it. Only leave enough records to cover the different types of data being searched for, and replace any sensitive data with fictitious data that still reflects the type of data and search criteria you're dealing with.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

Not suprised you piped up, Dale! You're responsible for getting me hot on code manipulation of the Advanced Filter in the first place. If I'm VBAjedi, you must be my AdvancedFilterYoda!

LOL

You'd probably be proud to know I took the methods you showed me and ran with them. I now have a tool that applies user-specified filter criteria to three sheets (with three different data column layouts) at once, bounces the results to three hidden sheets, slices and dices them there, and generates a summary sheet with comparative statistics on the differences between the three (Sales data, Projection data, and Budget data).

Good stuff!

VBAjedi

 

[DaleWatson123321]

VBAjedi,

Yes, I'm proud indeed !!!

Thanks for beleiving in the power of the Advanced Filter and its capability of excellent data manipulation, particularly when combined with VBA.

Your endorsement will hopefully encourage others to follow.

Thanks again, and a STAR to recognize your achievements. ;-)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DPlank]

All sounds very useful guys!

Here's the twisty bit..I want to return a subsection of the row of results to a listbox.

That is sooo vague.

OK, What I'm coding is a Data Management Tool for project work. I have a data matrix in excel which has 30 -odd columns. My Userform has the various columns spaced out across the pages. I need to pull the values from all the comboboxes and for those not blank, run through the advanced filter (Think this is what you were saying?) with the criteria specified?

With the results, I need to return about 4 of the values to a listbox (the core search terms, if you will).

I need to be able to expand these into another Userform which holds a data record of its own. (ie I need to set the value of the textboxes on the userform to be those relevant to the search item found.)

Hope this makes things a little clearer. If anyone wants to see what I've done/help out let me know.

Dale, if I have no luck by the end of today, it'll wing its way to you. Thanks in advance.

David

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[DPlank]

And a star for VBAJedi and DaleWatson.....cos they're great.

[joinedupwriting]
"whaddaya mean, 'invalid tag'?!?"
[/joinedupwriting]

 

[DrBowes]

Dale

I have to disagree about Advanced Filter being super fast...

In a previous role I had to regularly dedupe a list of about 50,000 records.

I found it was faster (using code) to sort the list, assign a 1 to all those records that are the same as the one above in the next available column using a formula at the top and the the autofill method down to the bottom, paste the values over formulas, sort the list again, find the first 1 in the new column and delete from there down.

All of the above took a couple of seconds, the Advanced Filter took much longer!

DrB

 

[DaleWatson123321]

DrBowes,

Perhaps we need to "compare notes".

There might be a situation where Advanced Filter might NOT be as fast - perhaps in situations like you describe. Keep in mind however, that the "Sort" function that you utilize is actually a component of the (Advanced) Filter component.

There have been others at Tek-Tips who have confirmed that indeed Advanced Filter has been MUCH faster. This is ESPECIALLY the case when compared to routines that loop through the records.

I would "challenge" you and all other programmers to "check out" the FULL capabilities of the Advanced Filter.

Unfortunately Microsoft has not provided much in the way of documentation on the use of the Advanced Filter, so unless one has had previous experience with such functionality, then it's certainly more difficult to "get up to speed" with the Advanced Filter.

The previous experience I refer to is with Lotus 123, which had this Advanced Filter capability - only MUCH MORE SUPERIOR than (the current version of) Excel - over a decade ago (nearly a lifetime in the computer industry).

"For the record"... When Microsoft "simulated" the "Database/Advanced Filter" component of Lotus 123, they only copied ONE of the data manipulation functions - that of being able to filter or extract data. The functions they did NOT copy are: Data-Find, Data-Modify, Data-Delete, and Data-Append.

In addition, Microsoft also did not copy Lotus 123's (ancient) capability to have spreadsheet-based relational databases.

So, DrBowes, in terms of comparing the latest release of Excel to the ancient version of Lotus 123, I can CERTAINLY agree with you than Excel's Advanced Filter leaves MUCH to be desired !!!

Nonetheless, I firmly believe that for one to "ignore" the POWER of Excel's Advanced Filter, is to "short-change" oneself - because there is STILL a considerable amount of "data manipulation" capability that one can utilize.

The Advanced Filter is especially useful where a user has a (spreadsheet-based) database that requires generation of multiple reports based on the need to use multiple criteria to isolate the data for the reports.

There is essentially "no limit" to the number of fields that can be referenced in the criteria, and the number of simple and/or complex formulas that can be used in referencing the fields.

These same type of criteria can also be used by Excel's "database (math) functions" - e.g. =DSUM, =DCOUNTA, etc).

In Excel, I've set up applications where all the user has to do to generate a &quot;detail&quot; report of the &quot;data behind any database formula&quot; in a matrix summary report, is to simply click use <Control> E while the cursor is on the formula. This extracts the data to a SEPARATE sheet for viewing, and is ready-to-print.

I hope this &quot;background&quot; information on the use of the Advanced Filter will encourage you to explore the &quot;HIDDEN POWER&quot; of this component.

A final ... If anyone out there happens to have &quot;connections&quot; with Microsoft..., perhaps Microsoft could be persuaded into upgrading Excel to include the missing data manipulation functions. ...but I won't hold my breath .

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[VBAjedi]

David,

Don't want to leave you hanging here. . . what you want to do should be feasible. In broad terms:

1) Loop through textboxes checking for values, write found values to Advanced Filter Criteria field (Make sure to write them under a copy of the appropriate data column header).
2) Execute filter
3) Loop through the data rows, adding the four key values AND the row# from each visible row to your listbox.
4) When user selects a listbox entry, use the row# of that entry to populate your second form. Display second form.

You'll probably have a decent amount of code when all is said and done, but that's to be expected when you set out to build an app with this level of complexity. Take it step by step and you'll be there before you know it!

VBAjedi

 

[DrBowes]

Dale

I will explore the possibilities with Advanced Filter at the next opportunity.

I had never thought to enter multiple criteria in it previously.

 

[emhunter]

Thanks for this tip. I have used it to place a filtered worksheet into a listbox to enable the user to make a selection on a subset of data.

Only problem is that date values are reformatted when being displayed in a listbox.

Rather than adhering to the regional setting of dd/mm/yyyy on my PC (and in the original worksheet), the listbox displays any dates as d/m/yy.

Any ideas on how to ensure local sensibilities are respected?

 

[VBAjedi]

Trying to keep date values displaying consistently as they are transferred into and out of code, worksheets, and controls can be a real pain. . .

A lot depends on whether the original value is a text value or recognized by Excel as a date value.

Take a look at the Format command in VBA help. That may be your ticket.

Good luck!

VBAjedi

 

[emhunter]

VBAjedi,

Thanks for the tip in your second paragraph. Following your suggestion:
- I 'Dim'med the variable array as STRING to force Excel not to recognise as a date. Previously the array was an undeclared variant which makes the array highly adaptable (but in this case too adaptable).
- Then I 'ReDim'med it to the correct dimensions when I knew the size of my table array.

When converting a date to a string, Excel displays some regional sensitivity and this is preserved when the string enters the list box. Without doing this, the listbox date is displayed in what I imagine is Excel's raw, internal date format.

em.