search db using textbox input

[daver676]

I want to create a Userform in VBA to take in a value though user input, into a text box, then when the user clicks a CMd button on the form, it needs to be able to search a column on a worksheet, then return any matching
value to a separate worksheet in the same workbook. I know how to create the USerForm with a textbox and cmdbutton. What I don't know is how to make excel search for that information in a separate database type worksheet,
then return any matching values to another worksheet. Any advice is appreciated.

Thanks

Dave

 

[SkipVought]

Hi daver676,

You can use the Worksheet function MATCH to find the index of the value withing the lookup range. No match returns an error, so use a variant to assign the value.

Dim vLoolupIdx 
vLookupIdx = Application.Match(TextBoxValue,LookupRange,0)
If Not IsError(vLookupIdx) Then
   'use vLookupIdx to return any other value in that row
   Value1 = Application.Index(Value1Range,vLookupIdx,1)
   Value2 = Application.Index(Value2Range,vLookupIdx,1)
...
End If

Of course your values could be stored in an array.

Then write the values to your other sheet.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[calculus]

While all of that's good, it might be easier to simply use a filter.

Simply highlight the column you want to "search" and then click data>Filter>auto filter.

A nice drop down box with all the options in that column appears. When you make a selection from the drop down box, only those rows are shown. All other rows are hidden.

I'm not sure what you're trying to do, but this is much simpler and more dependable than code.

 

[daver676]

Hey Guys,

Thanks for the great advice. In the end, I just used an advanced filter. I couldn't use an auto filter because this process needs to run in a macro. All the user has to do is type in a part # in an input box, then the program returns all information about that part from a huge list in Excel.