I have an Excel spreadsheet with a table of products. The product name is made up from a series of Categories namely the RANGE in which it belongs plus the product SIZE, COLOUR and SHAPE.
To add a new product in a sheet called AddProduct I use lists to provide the alternatives within each category and concatenate to arrive a the Product Name in column I
The Products sheet is then updated and sorted. This bit works okay.

The next bit is where I'm struggling
I have a byRange1 sheet which only shows horizontally the range names (B2:AR) and below each range sits the Product Name.
With the AddProduct sheet, which can have up to twenty new products, I want to look down column B at the Range Names, match that Range name horizontally in the byRange1 sheet and add the new Product Name, which is in column I of the AddProduct sheet, to the foot of the appropriate column in the byRange1 sheet.
The following almost works but not quite!

For Each c In Ws1.Range("b31:b50") 'the RANGE name in AddProducts sheet
For Each d In Ws2.Range("b2:ar2") 'the RANGE name in byRange1 sheet
If d <> "" Then
If c = d Then
c.Offset(0, 7).Copy Ws2.Cells(Rows.Count, d.Column).End(xlUp).Offset(1, 0) 'copies product name to next empty row below appropriate RANGE name
Exit For
End If
End If
Next
Next

Any help would be gratefully received.