Nested for loops in Excel

pbarvind

New Member
Joined
Aug 18, 2011
Messages
28
Hey Everyone,

I'm having difficulty with writing nested for loops in VBA. Could someone help me out just a little by giving me the equivalent of the following algorithm?

for(i=1;i<200;i++)
{
for(j=1;j<1063;j++)
{
for(k=1;k<1063;k++)
{
if(table1(i,j)&&table1(i,k)!=0)
{
table2(i,j)=table2(i,j)+table1(i,k);
}
}
}
}

Essentially table 1 is a table with names in the A column and products they own correspondingly.

table 2, which i want to get, is a product vs product table that will tell me how many companies from table1 own both products x&y

Any chance you could help me out? I'd be very grateful!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
This is the syntax for the For-Next.

I don't quite follow what caparison you are making in the IF stament.

Code:
    For i = 1 To 199
        For j = 1 To 1062
            For k = 1 To 1062
[COLOR="Green"]                'If table1(i, j) = table1(i, k) Then
                '    table2(i, j) = table2(i, j) + table1(i, k)
                'End If[/COLOR]
            Next k
        Next j
    Next i

Perhaps if you gave a short example of the two tables with the expected results, there may be a better method than looping through all the cells in both tables.
 
Last edited:

pbarvind

New Member
Joined
Aug 18, 2011
Messages
28
Hi AlphaFrog,

Thank you for replying!

Let me attempt to give an example
<pre>
Table One
Biscuit Chocolate Wafer Milk
Abe 2 3 2 1
Brian 1 0 2 1
Charlie 0 2 1 3
David 3 2 1 0

I want to generate a table two that is of the form
Biscuit Chocolate Wafer Milk
Biscuit
Chocolate
Wafer
Milk

</pre>
where, for instance table2(biscuit, chocolate) will tell me
-> the number of people who own both biscuits and chocolates (where the answer will be 2, only abe and david own both biscuits and chocolates)

Can you tell me how I can achieve this?

Thank you!


. Could you tell me how I can achieve this
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;font-style: italic;;">Table One</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Biscuit</td><td style="font-weight: bold;text-align: center;;">Chocolate</td><td style="font-weight: bold;text-align: center;;">Wafer</td><td style="font-weight: bold;text-align: center;;">Milk</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="font-weight: bold;text-align: center;;">Abe</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;text-align: center;;">Brian</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;text-align: center;;">Charlie</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="font-weight: bold;text-align: center;;">David</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;font-style: italic;;">Results</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Biscuit</td><td style="font-weight: bold;text-align: center;;">Chocolate</td><td style="font-weight: bold;text-align: center;;">Wafer</td><td style="font-weight: bold;text-align: center;;">Milk</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;text-align: center;;">Biscuit</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="font-weight: bold;text-align: center;;">Chocolate</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;;">Wafer</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;text-align: center;;">Milk</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">3</td></tr></tbody></table><br /><br />

Code:
Sub Count_Both()

    Dim vData, vResults()
    Dim i&, j&, k&, counter&
    
    vData = Range("B3:E6")  'Input
    ReDim vResults(1 To UBound(vData, 1), 1 To UBound(vData, 2))

    For i = 1 To UBound(vData, 2)
        For j = 1 To UBound(vData, 2)
            For k = 1 To UBound(vData, 1)
                If vData(k, i) > 0 And vData(k, j) > 0 Then counter = counter + 1
            Next k
            vResults(i, j) = counter
            counter = 0
         Next j
    Next i
    
    Range("B10").Resize(UBound(vData, 1), UBound(vData, 2)) = vResults 'Output
    
End Sub
 

pbarvind

New Member
Joined
Aug 18, 2011
Messages
28

ADVERTISEMENT

Thank you AlphaFrog!

I'm sure I'll be able to play around with the code and get the results I'm looking for! :)
 

pbarvind

New Member
Joined
Aug 18, 2011
Messages
28
AlphaFrog,

The program works when I try it with a small dataset. However, when I attempt to use it on a larger sample set, like a 101*1000 table, for which I need to get an output table 1000*1000, the program tell me that the "subscript is out of range, error 9"

and points to this line: "vResults(i, j) = counter"

This is after I've changed the ranges in the program according to the new table

Could you tell me where I'm going wrong?

Thanks!
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
Sorry about that. Try this...

Code:
Sub Count_Both()

    Dim vData, vResults()
    Dim i&, j&, k&, counter&
    
    vData = Range("B3:E6")  'Input
    ReDim vResults(1 To UBound(vData, [COLOR="Red"]2[/COLOR]), 1 To UBound(vData, 2))

    For i = 1 To UBound(vData, 2)
        For j = 1 To UBound(vData, 2)
            For k = 1 To UBound(vData, 1)
                If vData(k, i) > 0 And vData(k, j) > 0 Then counter = counter + 1
            Next k
            vResults(i, j) = counter
            counter = 0
         Next j
    Next i
    
    Range("B10").Resize(UBound(vData, [COLOR="Red"]2[/COLOR]), UBound(vData, 2)) = vResults 'Output
    
End Sub
 

Forum statistics

Threads
1,136,274
Messages
5,674,774
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top