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()

x = 1
ThisWorkbook.Activate
Sheets("Combining_values").Select
    Do While Cells(x, 17).Value <> " "
    Cells(x, 1).Value = Cells(x, 16).Value & " " & Cells(x, 17).Value
    'Range("A" & x & ":A" & x + 1).Merge
    x = x + 1
    Loop
    End Sub

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,
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.

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

Regards
Juan