Macro to filter a specific range with multiple vlookup return value(s)

Feardis

New Member
Joined
Jan 23, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,

I'm a beginner with VBA and on this forum, so bare with me here
smile.gif
I'll explain what I need the best I can and answer questions if my request is not clear.
Thanks in advance for any help provided!
I'm running Windows 10 with Office 365.

I have 3 different sheet within the same workbook ("Dash Board", "First Occurence" and "BOM").
The Dash Board sheet is where I have all the visual information for the follow up of a manufacturing project.
The First Occurence sheet is an Item Shortage report with important information about each shortage lines.
The BOM sheet is the entire BOM structure from top to bottom of a final item.

I want to run the macro from the Dash Board using multiple buttons (one for each manufacturing structure), but for the sake of making it simple, I will only explain it as if I need 1 button/macro.
I'll be able to replicate those buttons/macros that will do exactly the same thing as all the other ones with the exception of the lookup value itself.

I want the macro to :
Select the First Occurence sheet, Clear any already applied filter in cell A6 (field 1) for the range of A6:V2000,
then Filter that same cell (or field 1) with multiple different values that can be found by performing a lookup of the cell 'Dash Board'!B2 against the array 'BOM'!E$10:G$4000.
Basically, the lookup value in 'Dash Board'!B2 will be made against column E (starting at E10 to E2500) and the value(s) to filter the cell A6 in the "First Occurence" sheet will be "col_index_num 3" (which will be found in G10 to G4000 from the BOM sheet).

The lookup could possibly have no value, only one OR multiple values so ideally, I would want the script not to create any VB errors in all 3 possible scenarios. Also if it has no value to filter with, I'd rather have it filter "Blanks" so that the shortage report returns nothing. I'm not sure how that would apply here in a VB Script, but if it can be a reference, just like in any vlookup, the lookup return value(s) I'm looking for to filter with has to be exact matches.

Right now, I have created a macro that does exactly what I have explained with the exception that it only filter 1 value that is specified in the VB script. Took me a bit to figure out but it does what I wanted back then.
Now adding all that complexity with a sort of dynamic automatic filtering of multiple value is above my skill levels... Here is my current script:

Sheets("First Occurence").Select
ActiveSheet.Range("$A$6:$V$2000").AutoFilter Field:=1
ActiveSheet.Range("$A$6:$V$2000").AutoFilter Field:=1, Criteria1:= _
"4505E803-02"

So "4505E803-02" is what is being filtered in cell A6 in the First Occurence sheet. I'm trying to get something much more dynamic and automated for myself.

Thank you for whoever is taking the time.
It is much appreciated!

Vincent
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
The lookup could possibly have no value, only one OR multiple values so ideally, I would want the script not to create any VB errors in all 3 possible scenarios. Also if it has no value to filter with, I'd rather have it filter "Blanks" so that the shortage report returns nothing
Forgot to mention: if ithas value(s) to filter with no value(s) matching in A6:V2000 of sheet "First Occurence" then, I'd rather have it filter "Blanks" as well
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top