Hi there,
Hope everyone is doing well.
I am trying to create a macro which takes the value of a cell (based on the condition of it being "SEMI", "RAW", "PKG"). I then take the BOM No. which is in the first cell of the same column with the cell condition and use this cell value to filter column "E". (NO.) I do this for every condition met until there is no more condition to check for.
There are a lot more rows then this (35K Plus so I could not use the XLBB Tool).
Here is what the sheet looks like:
Basically, I am currently at the stage of using a FOR EACH loop that checks each if cell in column1 = "SEMI" or "PKG" or "RAW".... then it would select the Production BOM .NO (which in this case is CK00049) and use this cell value to filter column "E" (NO.) which would drill down the data, This loop will repeat again onto the next stage until a single "FG" is returned which then the value of the 'FG' could be returned into "RESULTS". Then I wish to reset the filter and move onto the next SEMI found.
On the second step of the drill down it would something like this:
As you can see: the bottom two rows (C:38165, C:38286) are tagged as semi, so I would have to again use these cell value and filter column "E" to drill down further until the final is returned. Using C:38165 this would be returned:
After this, I would copy and paste the first cell as a result, and then would drill back out to repeat with C:38286 (if not more). IF not the case, I would reset the whole thing, with the initial filter, and move onto to the next condition of it being "SEMI" or "RAW" or "PKG" and etc.
I understand that this will be a nested for each loop with multiple stages and with some complicated conditions, but I cant seem to figure if its even possible to print out all the conditional results in one go.
Here is the structure of what I have so far:
First search to see if product type condition is met.
if true: then take first cell in active column and use cell value as filter for column "E" (NO.)
then run search condition again
if it was false here, open new sheet with results
if true take cell again and filter down
if false: return "No queries found" end if and go to end code
then
run search condition again
if true take cell again and filter down
if anyone can point me in the right direction or create a piece of code that can loop at least once, it will be greatly appreciated.
I am pretty stuck with this so please help out!
Hope everyone is doing well.
I am trying to create a macro which takes the value of a cell (based on the condition of it being "SEMI", "RAW", "PKG"). I then take the BOM No. which is in the first cell of the same column with the cell condition and use this cell value to filter column "E". (NO.) I do this for every condition met until there is no more condition to check for.
There are a lot more rows then this (35K Plus so I could not use the XLBB Tool).
Here is what the sheet looks like:
Basically, I am currently at the stage of using a FOR EACH loop that checks each if cell in column1 = "SEMI" or "PKG" or "RAW".... then it would select the Production BOM .NO (which in this case is CK00049) and use this cell value to filter column "E" (NO.) which would drill down the data, This loop will repeat again onto the next stage until a single "FG" is returned which then the value of the 'FG' could be returned into "RESULTS". Then I wish to reset the filter and move onto the next SEMI found.
On the second step of the drill down it would something like this:
As you can see: the bottom two rows (C:38165, C:38286) are tagged as semi, so I would have to again use these cell value and filter column "E" to drill down further until the final is returned. Using C:38165 this would be returned:
After this, I would copy and paste the first cell as a result, and then would drill back out to repeat with C:38286 (if not more). IF not the case, I would reset the whole thing, with the initial filter, and move onto to the next condition of it being "SEMI" or "RAW" or "PKG" and etc.
I understand that this will be a nested for each loop with multiple stages and with some complicated conditions, but I cant seem to figure if its even possible to print out all the conditional results in one go.
Here is the structure of what I have so far:
First search to see if product type condition is met.
if true: then take first cell in active column and use cell value as filter for column "E" (NO.)
then run search condition again
if it was false here, open new sheet with results
if true take cell again and filter down
if false: return "No queries found" end if and go to end code
then
run search condition again
if true take cell again and filter down
if anyone can point me in the right direction or create a piece of code that can loop at least once, it will be greatly appreciated.
I am pretty stuck with this so please help out!
Last edited by a moderator:
