Rob Webster
New Member
Hi All,
I'm fairly new to VBA but have managed to automate a few tasks in the past, at the minute I'm wanting to speed up a very time consuming currently manual activity carried out on a spreadsheet.
I have thousands of items in a list down the page, each having a total price in a column. but the items are summary items and are made up of individual parts. some having only one part and others having as many as 20 - 30 from a list of nearly a thousand
I have a description of the individual parts in one row near the top, and their part ID numbers above it
in each item row the applicable parts that make up the item are priced.
what I need to do is to adjust the list so that the parts making up the item are listed beneath the item in the main list.
I've attached an example but have based it on a food menu just to get the point across.
the list of items I am having to deal with is in excess of 5000, and there are about 1000 individual part numbers.
the final document represents a quotation with price breakdown for parts and labour. (in a printer friendly view)
my logic thoughts to date are to control this with a loop and a nested array
1) activate cell containing the total value of an item
2) count the ingredients Parts, to ascertain how many there are.
3) insert "N" rows below the active row in the list to accommodate the parts.
4) populate the new rows with the price in the total value column and the part number in the part number column and the part name in the description column
5) loop through the row, to get the prices of the parts (which may may be randomly spaced, or may be side by side. there may also only be 1 part
in the event that there is only 1 part, no rows should be added but the part id number should be placed beside the total price and the description ignored.
6) when all the parts are completed for the item, loop to the next item
not sure if this is the right way to do it - it might be quicker to fill an array with the values and then drop them in the new rows -? but I've uploaded an example file for review.
I'm fairly new to VBA but have managed to automate a few tasks in the past, at the minute I'm wanting to speed up a very time consuming currently manual activity carried out on a spreadsheet.
I have thousands of items in a list down the page, each having a total price in a column. but the items are summary items and are made up of individual parts. some having only one part and others having as many as 20 - 30 from a list of nearly a thousand
I have a description of the individual parts in one row near the top, and their part ID numbers above it
in each item row the applicable parts that make up the item are priced.
what I need to do is to adjust the list so that the parts making up the item are listed beneath the item in the main list.
I've attached an example but have based it on a food menu just to get the point across.
the list of items I am having to deal with is in excess of 5000, and there are about 1000 individual part numbers.
the final document represents a quotation with price breakdown for parts and labour. (in a printer friendly view)
my logic thoughts to date are to control this with a loop and a nested array
1) activate cell containing the total value of an item
2) count the ingredients Parts, to ascertain how many there are.
3) insert "N" rows below the active row in the list to accommodate the parts.
4) populate the new rows with the price in the total value column and the part number in the part number column and the part name in the description column
5) loop through the row, to get the prices of the parts (which may may be randomly spaced, or may be side by side. there may also only be 1 part
in the event that there is only 1 part, no rows should be added but the part id number should be placed beside the total price and the description ignored.
6) when all the parts are completed for the item, loop to the next item
not sure if this is the right way to do it - it might be quicker to fill an array with the values and then drop them in the new rows -? but I've uploaded an example file for review.