VBA: Using Nested Loops to expand a summarised table.

[Paddycrook]

Hi Everyone

I would like to start off by saying I am a complete newbie to VBA. I have been trying to do this for hours now and just ended up confusing myself.

As far as I can tell there needs to be a nested loop with some kind of variable inside a variable, but to be honest it's well beyond my current skill level... I thought rather than confusing everyone reading this post and embarrassing myself with my poor code, I would just put the before and after tables to simplify things. It might also be less complicated than what I thought. If anone feels like helping me out I would be very greatful

I have the file hosted on my Google Docs below.
https://docs.google.com/file/d/0B73mVtKj5WFvY0RDQzZBYTJfb0k/edit?usp=sharing

Thanks!
Paddy

[TABLE="class: outer_border, width: 490"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Item[/TD]
[TD]Quantity[/TD]
[TD]Costs[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Cheese[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Butter[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Pencils[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cheese[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Paper[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Meat[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]£50[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Apples[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]£100[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpast[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]£100[/TD]
[/TR]
</tbody>[/TABLE]


to

[TABLE="class: outer_border, width: 247"]
<tbody>[TR]
[TD]Company
[/TD]
[TD]Item[/TD]
[TD] Costs [/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Cheese[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Cheese[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Cheese[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Cheese[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD] £ 10.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD] £ 10.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD] £ 10.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD] £ 10.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Milk[/TD]
[TD] £ 10.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Bread[/TD]
[TD] £ 7.14[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Butter[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Tesco[/TD]
[TD]Butter[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Pencils[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Pencils[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Pencils[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cabbages[/TD]
[TD] £ 5.56[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cheese[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Cheese[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Balls[/TD]
[TD] £ 6.25[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Paper[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Paper[/TD]
[TD] £ 25.00[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Meat[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Meat[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Meat[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]Meat[/TD]
[TD] £ 12.50[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Pens[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Goats Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Crabs[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Apples[/TD]
[TD] £ 33.33[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Apples[/TD]
[TD] £ 33.33[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Apples[/TD]
[TD] £ 33.33[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Oranges[/TD]
[TD] £ 20.00[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Milk[/TD]
[TD] £ 11.11[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
[TR]
[TD]Morrisons[/TD]
[TD]Toothpaste[/TD]
[TD] £ 16.67[/TD]
[/TR]
</tbody>[/TABLE]

 

[ChrisM]

Give this code a shot. You need to highlight your desired range, including headers, then run this code. It will output the results three rows below your highlighted section (and will overwrite anything there)

Sub test()

Dim rRange As Range
Dim rOutput As Range
Dim lRows As Long
Dim aTemp As Variant
Dim i As Long, k As Long
Dim sCurrCo As String
Dim lcount As Long
Dim dCurrCost As Double

Set rRange = Selection 'A1:Dxxx

lRows = Application.WorksheetFunction.Sum(rRange.Resize(, 1).Offset(1, 2))

ReDim aTemp(1 To lRows + 1, 1 To 3)

aTemp(1, 1) = rRange.Range("A1") 'write headers
aTemp(1, 2) = rRange.Range("B1")
aTemp(1, 3) = rRange.Range("D1")

lcount = 2
For i = 2 To rRange.Rows.Count
    dCurrCost = rRange.Cells(i, 4) / rRange.Cells(i, 3)
    For k = 1 To rRange.Cells(i, 3)
        aTemp(lcount, 1) = rRange.Cells(i, 1) 'company
        aTemp(lcount, 2) = rRange.Cells(i, 2) 'item
        aTemp(lcount, 3) = dCurrCost 'cost per
        lcount = lcount + 1
    Next k
Next i

'now write array to sheet, 3 rows below source range
Selection.Resize(1, 1).Offset(rRange.Rows.Count + 3, 0).Resize(lRows + 1, 3) = aTemp 'this is really ugly!!

Set rOutput = Nothing
Set rRange = Nothing

End Sub

 

[ChrisM]

I should have read the range in to an array, I don't know why I read it into a Range object, I was thinking along different lines at the beginning I guess. Oh well, it still works fine.

 

[Paddycrook]

Thanks! I have no clue how this works but it does Will probably do a bit of homework just working out the hows and whys of it. Really great stuff though - much appreciated!