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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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

J
  • Solved
Selenium VBA - loop to align new data with old data in worksheet
Replies
8
Views
385
Anthony47
A
J
  • Solved
Excel Selenium VBA - Help with loop when class appears more than once
Replies
3
Views
236
Anthony47
A
B
  • Solved
Do Until loop nested in For loop, How advance to the "Next x" in the For Loop
Replies
4
Views
587
CephasOz
CephasOz
AOB
  • Question Question
ListObject.ListColumns.Add fails if Excel application is not visible and ScreenUpdating not enabled
Replies
3
Views
78
Domenic
D
G
  • Question Question
VBA help - copy cells from specific table to specific table on another sheet.
Replies
0
Views
760
GardianAngelUK
G

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

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