Loop through a matrix

nada

Board Regular
Joined
Jul 4, 2006
Messages
193
Hi! I have a matrix. The matrix is populated with info for a long list of firms. For evey firm there is an array filled with info about the firms rating. The ratings are placed in priority order ie the the lower the index number the higher the priority. For every firm I want to check the rating. Starting with the highest prio and then moving down I want to find only one rating. Thus first i want to check index = 0 if that place does not contain any rating then i want to check index = 1 etc. However I seem to have lost myself in my loop....

For i = 1 To UBound(varBBAnswerMatrix, 1)
For j = 1 To UBound(varBBAnswerMatrix, 2)
If IsRatingOK(varBBAnswerMatrix(i, j)) Then
'here I have found a rating. Move on to the next firm
Exit For
Else
j = j + 1
Next
please help me with this loop! thanks alot!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi Nada

There are 2 things you did no say.

- What do you want to do in case you find the rating the firm

- What do you want to do in case you do not find the rating

Anyway if I understand what you are doing you should not have the statements

Else
j = j + 1

because the For automatically increments j. This statement would case j to be incremented twice in the same loop.

also you have 2 syntax errorr, you don't close the IF block and you forgot the second Next.

Code:
For i = 1 To UBound(varBBAnswerMatrix, 1) 
    For j = 1 To UBound(varBBAnswerMatrix, 2) 
        If IsRatingOK(varBBAnswerMatrix(i, j)) Then 
                ' here I have found a rating.
                ' Do something and move on to the next firm 
            Exit For 
        End If 
    Next
    If j = UBound(varBBAnswerMatrix, 2) + 1
        ' No rating found for this firm
        ' Do something and move on to the next firm 
    End If
Next

If you are sure that finding no rating is impossible you don't need the seconf If.

Hope this helps
PGC

P. S. I assumed that you are sure that the lowest indexes are 1 and not 0
 

Forum statistics

Threads
1,141,587
Messages
5,707,242
Members
421,498
Latest member
matinebi

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