Help with a VBA Search function across multiple sheets to return a match

[UMAKEMESIK]

TO ALL,

I have a workbook with multiple sheets.
The top level sheet is where I enter information to
Look for matching data on the following sheets.

I use Vlookup for an exact match when I know the part number and it works great.

I would like to add a search function for when I don’t know the part number
And am looking for a match of Length Width and depth –

The returned result would be:
Part number – Length – widty and depth.

I would also like to add a variance of + 1 and -1
So the result would find all units with the exact match and all those over and under by 1”

I would like this to run when the user enters a number and presses ENTER. ( or a click of a button would be ok to run the command)

So the user would enter 12 – 3 – 10 in three different cells all on the same line – Press Enter
And the result would show up possibly on the same line
But then all of the units over and under could show up right below.

I am pretty sure I will need VB for this as I think this goes beyond what Vlookup can accomplish.

I can explain more if needed.

Thanks in advance.

 

[Jerry Sullivan]

Hi Martin,

You can adjust the variance at the beginning of the code...

 '--maximum variance to be considered near match
 Const dVARIANCE As Double = 3

You should be able to delete column E from all your sheets.
The named range "ptrStockBoxSizeSearchHeaders" on sheet "Stock Box Lookup" defines the headers. When you delete column E from all sheets, that named range will shrink by one column and that should keep everything in sync.