Listing Item Details

nickharr · Jun 30, 2011

I wanted to try and put in a formula that would make a list of certain products. It is beyond me but possibly someone could help.
To simplify matters, I have a sheet containing several rows. In the first row in A1, I have a product name. In B1, a quantity of this product. If the value in B1 is 5, I need a list of the product name and the quantity of 1 listed 5 times in separate rows (presumably in a different sheet). This I can do okay but the problem is when A2 has a different product name and B2 has say a value of 10 - I would be looking for 10 new rows to be added to the sheet which already has the 5 rows in it.
Hopefully I have explained this enough and I would be very grateful if anybody could tell me how to achieve this.

John Davis · Jun 30, 2011

nickharr said:
 I wanted to try and put in a formula that would make a list of certain products. It is beyond me but possibly someone could help.
To simplify matters, I have a sheet containing several rows. In the first row in A1, I have a product name. In B1, a quantity of this product. If the value in B1 is 5, I need a list of the product name and the quantity of 1 listed 5 times in separate rows (presumably in a different sheet). This I can do okay but the problem is when A2 has a different product name and B2 has say a value of 10 - I would be looking for 10 new rows to be added to the sheet which already has the 5 rows in it.
Hopefully I have explained this enough and I would be very grateful if anybody could tell me how to achieve this.

Not sure I fully understand you, but maybe?

Code:

Sub nickharr()
Dim i As Long
Dim lr As Long
Dim x As Long

lr = Cells(Rows.Count, 1).End(3).Row

For i = 1 To lr

x = Range("B" & i).Value

Do While x <> 0

Range(Range("A" & i), Range("B" & i)).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

x = x - 1

Loop

Next i

End Sub

nickharr · Jul 1, 2011

John - many thanks for your reply. As I am a complete novice with codes - could you just run me through the process - am I correct in thinking I open a new sheet go to view codes and paste in your code. Then go to A1 in sheet1 and type a name, then to B1 and put in a quantity. Then do the same in A2 and B2 and the results should be on Sheet2? I have obviously tried this but at the moment - no luck!

nickharr · Jul 1, 2011

John - It works!! I was being very foolish and not actually "running" the macro on Sheet2. But now I have and it works just great - thanks so much!

nickharr · Jul 1, 2011

John - not sure if you get this message but I just wondered how to expand your code slightly so that it could include information in the same row. In other words if I had a price in C1 and and measurement in D1 - how could I get these showing on Sheet2. I did think that I should be able to work this out myself and tried but failed!
I would really appreciate if you could help.

John Davis · Jul 5, 2011

nickharr said:
John - not sure if you get this message but I just wondered how to expand your code slightly so that it could include information in the same row. In other words if I had a price in C1 and and measurement in D1 - how could I get these showing on Sheet2. I did think that I should be able to work this out myself and tried but failed!
I would really appreciate if you could help.

Sorry Nick. I was off for a couple of days. Do you want the entire row copied to Sheet2?

If so change this line:

Range(Range("A" & i), Range("B" & i)).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

To this:

Range("A" & i).Entirerow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp)(2)

nickharr · Jul 5, 2011

I must have sat looking at the code for about an hour thinking I must be able to work this out but I couldn't! Then you solve my problem in a few minutes!! It works just fine now and is doing exactly what I needed - thanks so much!

John Davis · Jul 5, 2011

nickharr said:
I must have sat looking at the code for about an hour thinking I must be able to work this out but I couldn't! Then you solve my problem in a few minutes!! It works just fine now and is doing exactly what I needed - thanks so much!

You're welcome, glad it worked for you. Thanks for the feedback.

Listing Item Details

nickharr

Active Member

Excel Facts

John Davis

Well-known Member

nickharr

Active Member

nickharr

Active Member

nickharr

Active Member

John Davis

Well-known Member

nickharr

Active Member

John Davis

Well-known Member

Similar threads

Forum statistics

Share this page

Listing Item Details

Active Member

Excel Facts

Well-known Member

Active Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock