Nested for loops in Excel

P

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

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
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:
Upvote 0
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:
Upvote 0
<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
 
Upvote 0
Thank you AlphaFrog!

I'm sure I'll be able to play around with the code and get the results I'm looking for! :)
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Do Until loop nested in For loop, How advance to the "Next x" in the For Loop
Replies
4
Views
245
CephasOz
CephasOz
G
  • Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
239
GardianAngelUK
G
V
  • Question
run vb-application to search string in the table to "score"
Replies
0
Views
50
vic_it
V
K
  • Question
Comparing two sheets
Replies
3
Views
849
Killragtshirts
K
W
  • Solved
Moving/transferring data between tables with matching column names, not by column order - vba
Replies
5
Views
209
Alex Blakenburg
A

Forum statistics

Threads
1,214,628
Messages
6,120,618
Members
448,973
Latest member
ChristineC

Share this page

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
Back
Top