Creating a searchable database using VBA?

[sarvatra]

Below is an inventory database I have created (I hope you all can see the image).

I am trying to create a search form where if enter the number "10-45104" in it, it will display "Rack A" in the result field.

Essentially, I want to search for the number between the two numbers provided in the Rack A column. (I would also like to have multiple search fields to carry out multiple searches.)

I don't have enough programming knowledge about excel to create such a formula. Is it possible to create a VBA script for it?

I hope this makes sense. Thanks in advance!

 

[sarvatra]

After doing some more research, I think the best way to go about this is to create a userform searchbox. I am going to change my database to include all the ID numbers instead of just the beginning and ending of the range.

Can anybody help me with the code for the userform? I am a complete novice when it comes to VBA, so any help would be appreciated.

 

[Mulkis]

Hi,
I am not a big expert but in your case I would use simple HLOOKUP function with no VBA.

 

[sarvatra]

Thanks for the reply.

This is my database:

So I tried using the HLOOKUP formula:

=HLOOKUP(7-14355, A1:F8, 1, FALSE) which gave me #N/A instead of giving me "Rack 5" as the result...

I realized that when I type 07-14355, it is automatically changed to 7-14355, which might be the problem. How can I fix that?

What am I doing wrong here?

 

[Mulkis]

Sarvatra,
The problem with HLOOKUP is that it looks for the value only in the top row of the table. So in order to use it in case like yours you will need a dynamic range. In addition, you will need to copy all your column headers to the last row of the database.
Ok, lets go thought an example:
Your database is A1:C8
Value to search D1 (Value)
Search result D2
Firstly, find the row and the column of the Value (array formulas, use Ctr+Shift+Enter):
In D8: =MAX(0;IF(A2:C7=D1;ROW(A2:C7)))
In F8: =MAX(0;IF(A2:C7=D1;COLUMN(A2:C7)))
Then define dynamic range: Insert-Name-Define
Type name WW.
Refers To type formula: =OFFSET(Sheet1!$A$1;Sheet1!$D$8-1;0;Sheet1!$D$1;3)
And at last the HLOOKUP in D2: =HLOOKUP(D1;WW;9-D8;FALSE)
Type value to search in D1 and excel will show result in D2.
Don’t forget to copy columns headers (Rack1, etc.) in the last row of the table (Row 8 in my example).
You will need some tweaking to adapt this method for your specific database, but it works 100%. I use it myself. It might be more elegant way, but I am happy with his.
Hope it will be of use for you.

 

[sarvatra]

Elegant indeed. Is there a simpler solution? If not, thanks!