Great community. Great ideas.
Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.
February 14th, 2013, 08: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.
Last Post: March 30th, 2012, 03:46 PM
By JJSJ in forum ASP Development
Last Post: February 16th, 2010, 04:58 AM
By yogaboy in forum Microsoft SQL Server
Last Post: February 7th, 2006, 07:12 AM
By jmjj215 in forum Microsoft Access Help
Last Post: May 6th, 2004, 05:05 PM
By WadeO in forum ASP Development
Last Post: February 7th, 2003, 03:50 PM