Embedded For Loops

[arsmith646]

Hello,

I am a self-taught VBA writer, and my work is wanting me to do something beyond my knowledge. I have a table that contains Vendor, Platform, Model, Category, and Component. I need to have a list off to the side containing a list of Platforms and Components that can be added to, but the program needs to be able to search the table for the Platform and Component and store as well as concatenate and add each Model on a new line inside of one cell. The headings look like this.


[TABLE="width: 1932"]
<tbody>[TR]
[TD="class: xl71, width: 68"]Vendor[/TD]
[TD="class: xl70, width: 300"]Platform[/TD]
[TD="class: xl70, width: 824"]Model[/TD]
[TD="class: xl70, width: 86"]Category[/TD]
[TD="class: xl69, width: 95"]Component[/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="width: 72"][/TD]
[TD="class: xl68, width: 253"]Platform[/TD]
[TD="class: xl68, width: 90"]Component[/TD]
[/TR]
</tbody>[/TABLE]


The concatenated information needs to be then pasted into another worksheet in the corresponding Platform header and Component row. Please let me know if you need any clarification. Any help is appreciated. Thank you in advance!

 

[Kuljack]

Hello Arsmith,

What would be the function of this side bar effort?

Does it filter the table down by selection of platforms/components or does it add to the table more data?

 

[arsmith646]

Hello Arsmith,

What would be the function of this side bar effort?

Does it filter the table down by selection of platforms/components or does it add to the table more data?

The side bar allows the user to add more search criteria and provides a list of criteria to find within the main table. Does that make sense?

 

[Kuljack]

Maybe...

So if for example from the side bar I choose blue then more details may show up saying since I choose blue I now need to capture details about the size and height of blue, but if I chose yellow I may only need to find the size?

 

[arsmith646]

Maybe...

So if for example from the side bar I choose blue then more details may show up saying since I choose blue I now need to capture details about the size and height of blue, but if I chose yellow I may only need to find the size?

It needs to run through the first Platform and each Component while finding and saving each combination's Model and concatenating the Models to be pasted into the corresponding Platform column and Component row on another sheet.

 

[Kuljack]

Sounds very doable, can you take the time to just provide the steps step-by-step on how you would see this playing out for the user so I can be certain I have a clear picture.

Be sure to be clear on when it's the user taking action versus the VBA, and also will the product on the second sheet be a one time population, or will it reoccur multiple times?

Like, if I follow these steps in sheet two just giving the answer needed, or would you be generating a serious of multiple answers?

 

[arsmith646]

Sounds very doable, can you take the time to just provide the steps step-by-step on how you would see this playing out for the user so I can be certain I have a clear picture.

Be sure to be clear on when it's the user taking action versus the VBA, and also will the product on the second sheet be a one time population, or will it reoccur multiple times?

Like, if I follow these steps in sheet two just giving the answer needed, or would you be generating a serious of multiple answers?

I am hoping that the User will only have to click a button with the programmed macro.

I would like the macro to run through each Platform in column I to the platform in column B as well as the corresponding Component in column J with the Component in column E, then return, concatenate, and insert each value matching in column C on a new line (Char(10)) that is determined by finding the column with the corresponding Platform and Component that was searched.

So in other words, I'm imagining a loop that runs through each Platform, say Platform1, and each Component, the first Component1, then goes to the table and finds all phrases in column C (Model) that matches the criteria Platform1 and Component1. It will then concatenate, add a Char(10) between each concatenation, then paste the new phrase in the other worksheet based on the criteria searched (Platform1-column, and Component1-row).

After this it will loop through again, searching for the Platform1, Component2 (and repeat the process above). After reaching a blank line in Component, it will need to advance to the next Platform then repeat the process through all Components and so forth until the Platform has reached a blank and the process is complete.