February 14th, 2013, 09:34 AM
Match vertical data to horizontal data and copy to foot of column
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
Any help would be gratefully received.