Hello everyone,
I'm a beginner with VBA and on this forum, so bare with me here
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
I'm a beginner with VBA and on this forum, so bare with me here
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
