Macro with nested loop to combine each item of a colum with multiple items of another col

Hi All,
I have two columns with data as follows:

Column A:
Shoe white
Shoe Black
shoe Red

Column B:
Size 6
Size 7
Size 8
Size 9
Size 10

I need to combine (concatenate) them as follows:


Shoe white Size 6
Shoe white Size 7
Shoe white Size 8
Shoe white Size 9
Shoe white Size 10
Shoe Black Size 6
Shoe Black Size 7
Shoe Black Size 8
Shoe Black Size 9
Shoe Black Size 10
shoe Red Size 6
shoe Red Size 7
shoe Red Size 8
shoe Red Size 9
shoe Red Size 10

I tried the following code:


Sub proCombineUnevenColumnsVariables()

Please Login or Register  to view this content.

I have a couple of problems I would appreciate some help with:

1.The code above gets a Run-time error:'13' Type Mismatch" error message. Besides the loop never ends, it executes even for empty cells (which is against the condition of the While loop) and I have to re-start excel to get out of it.
2. The code above only solves one part of the problem (it merges 2 ranges) but I still need to work out how to nest it with another loop to concatenate each cell of one range with ALL CELLS of the other range.


I would appreciate if someone could point me in the right direction.

you all have a lovely day.

Cheers,
Juan

Hi Richard,
Thanks a lot. It worked great and the exercise of deciphering is a real eye opener.
Would you have a second to look at this attached image?
Description of the requirement.jpg

Your solution is great, I thought I needed a loop because (besides I didnt know the stuff you showed me) I also need to drag some columns related to column A and put them next to the concatenated result (the one your formula puts under C).
so for example if:
column A = Colour
and next to it we had
column B=price
Column C =Availability
Column D = size

then the result I am after is:
Column F= A+D (which your formula gives me)
and next to each item on column F I will need the corresponding value on B, C and D.

Would you have a second and point me in the right direction?
Have good day

cheers.
Juan

Hi Richard,
Sorry Im not making much sense here. I'll try again:

The range F2:H39 has the categories and subcategories in which all the ITEMS come in. So for each item I will have 38 rows. For those 38 rows I will have:

Column B: Is the item name which is simply =concatenate(K2,L2)... no combinations or permutation. Concatenate(K2,L2) repeats itself along 38 consecutive rows along column B.
Column C: is the manufacturer wich in this case is M2. so M2 repeats itself down 38 consecutive rows on column C
Columns D and E are to be ignored
Columns F, G and H from row2 to row 39 (38 rows) get copied and placed next to the ITEM.

Then we do the same for the next item:
Column B: =Concatenate (K3,L3). which repeats from row 40 to row 77
Column C = M3. repeated from row 40 to row 77
Column F and G = os F2:H39 placed next to the the above on rows 40 to 77

then we do the same with concatenate(K4:L4). we place them between rows 78 and 115. along column B
M3 repeats itself 38 times between C78 and C115
F2:H39 get copied over to F78:F115

Hopefully this is clearer now. Please let me know if it isnt and apologies if Im wasting your time.

Kind regards,
Juan

Hi Richard,
Was my response clearer? Please let me know
Have a great day.

Regards
Juan

Hi Richard,
Both lists K2:M16 and F2:H39 are given. they are literally just typed in. F2:H39 are 38 rows because that is the amount of data I have.

Cheers.