Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search entire workbook, use contain and fields in search

0

I found this very great giude by TeachExcel and it allmost covers what I need, but I can't seen to get the last things to click.
I'm trying to use fields like he is bot it wonøt search in the second choice (3th collum)

'Get user input
searchValue = LCase(dashboard.Range("B3").Value)
FieldValue = dashboard.Range("C3").Value

'Clear Dashboard
Call Clear_Data

'Figure out by which field we will search.
If (FieldValue = "Sag") Then
    searchField = 2 'Works fine
ElseIf (FieldValue = "Bygherre") Then
    searchField = 3 'Does'nt work
End If

On to of that I need it to search the collum to see if it contains the search value.
Example:
If I search for
House it should return all rows the the collum contains "house" like "This House", "Red House", "House shop", "Big red House"

excel
MS Office Version: Excel 365
posted by: Rackneck (rep: 6) on Tue Aug 30, 2022 at 3:53 pm
Answer
Discuss

Answers

0
Selected Answer

Rackneck

You found a code that was altered to work for a partial search with searchfield=2 only, i.e. the test is this:

If searchField = 2 And LCase(Left(dataArray(i, searchField), Len(searchValue))) = searchValue Then

In the attached revised file, that is fixed and the "wildcard" search (case-insensitive) implemented by commenting out that line and replacing it with the one in bold below:

'Check if the value in the row equals our search value
            'If (dataArray(i, searchField) = searchValue) Then
            'If searchField = 2 And LCase(Left(dataArray(i, searchField), Len(searchValue))) = searchValue Then
            ' Use wildcard search
            If LCase(dataArray(i, searchField)) Like LCase(searchValue) Then

Now the search for Bygherre (searchfield=3) works (like Sag) with full names but you can use wildcards too.  If you search for Sag =  "*Yard*", it returns records for "Sales yard" and "Scrap yard" since the asterisk (*) is a wildcard meaning none or more characters (either side of Yard). If you search for Bygherre = "*g*y*", you will get any records with a "g" followed by a "y" so This guy, old guy, Big lady, Test guy, Fall guy, Tougt lady, Guy Forbes, Big company, Big company, old guy, Big lady, Test guy and Fall guy from your file.

There are other wildcards which work in Excel. ? is a useful one meaning just one character (so g??l would return girl and goal but not gravel). 

Hope this fixes things for you.

posted by: John_Ru (rep: 6142) on Wed Aug 31, 2022 at 6:49 am
Discuss

Discussion

Thank you again John, you sir are a god. You are 2 for 2  on my questions. I'm curious tho, 2 things.... 1. What did I miss since my 
ElseIf (FieldValue = "Bygherre") Then
  searchField = 3 

didn't work, what did i over look ? 2. Is there there anyway to automaticly add the * before and after every search in the macro ?
Rackneck (rep: 6) Sep 1, '22 at 2:26 pm
I'm happy that worked for you, Rackneck. Thanks for selecting my Answer (but your praise is too much!).

On your extra questions...

1) you did nothing wrong with that line; it would have worked (for a full string match) with the original TeachExcel code which used the (commented out) line:

If (dataArray(i, searchField) = searchValue) Then


but (as I said) the code was modified to add a  new line (which I highltighted in my Answer) for a user only interested in searchfield=2.

2) If you really always want the * wildcards either side of whatever is entered in B3, change the line:
'Get user input
searchValue = LCase(dashboard.Range("B3").Value)

to read (changes in bold):
'Get user input
searchValue = "*" & LCase(dashboard.Range("B3").Value) & "*"

I would suggest that you add a note (in Danish I guess) to your spreadsheet so that users understand that it will always return a search showing whatever records include that string, not just  exact matches.
John_Ru (rep: 6142) Sep 1, '22 at 6:00 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login