• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

understanding Loops with nested Arrays.

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.
 

Attachments

  • Book1.xlsx
    12.5 KB · Views: 5
Try
Code:
Sub test()
    Dim a, b, i As Long, ii As Long, iii As Long, n As Long
    With Sheets("sheet1")
        With .Range("a5", .Range("a" & Rows.Count).End(xlUp)).Resize(, .Cells.SpecialCells(11).Column)
            a = .Value: n = 3
            b = .Resize(.Rows.Count * .Columns.Count).Value
        End With
    End With
    For i = 4 To UBound(a, 1)
        n = n + 1: For ii = 1 To UBound(a, 2): b(n, ii) = a(i, ii): Next
        For ii = 7 To UBound(a, 2)
            If a(i, ii) <> Empty Then
                n = n + 1: b(n, 1) = Empty: b(n, 2) = a(2, ii)
                b(n, 3) = a(i, ii): b(n, 4) = a(1, ii)
                For iii = 7 To UBound(b, 2): b(n, iii) = Empty: Next
            End If
        Next
    Next
    Sheets.Add.Cells(1).Resize(n, UBound(b, 2)).Value = b
End Sub
 

Attachments

  • Book1 with code.xlsm
    23.2 KB · Views: 10
Hi, Thank you very much for that. works a treat. I just need to understand it enough to be able to incorporate it into my file now. :)
 
Back
Top