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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,452
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,452
<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
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,452
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,172,125
Messages
5,879,205
Members
433,409
Latest member
momo2809

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