Loop Not Completing All Loops

rico355 · Jul 2nd 2007

Im using the following code to assign numerical values to words in two columns, then multiplying the values together and painting a cell with a specific color assigned to the final number. The problem is it wont do this past row 19, any ideas as to why?

Suggestions for a better way to do this are VERY WELCOMED!

THANKS IN ADVANCE

Code

' Count till the last row
    mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row
    For m = mLastRow To 5 Step -1


    ' assign numerical values to the words in these columns
    e = 22
    r = 23
    
' Give Extent a value
If Cells(m, 9) = "Minor" Then
Cells(m, e) = 1
End If
If Cells(m, 9) = "Moderate" Then
Cells(m, e) = 5
End If
If Cells(m, 9) = "Serious" Then
Cells(m, e) = 8
End If
If Cells(m, 9) = "Critical" Then
Cells(m, e) = 10
End If


'Give Risk a value
If Cells(m, 10) = "Low" Then
Cells(m, r) = 1
End If
If Cells(m, 10) = "Medium" Then
Cells(m, r) = 5
End If
If Cells(m, 10) = "High" Then
Cells(m, r) = 10
End If




'Multiply values together
Cells(m, 24) = Cells(m, e) * Cells(m, r)


' Color cells according to value
If Cells(m, 24) >= 50 Then
Cells(m, 24).Interior.ColorIndex = 3
End If
If Cells(m, 24) >= 25 And Cells(m, 24) < 50 Then
Cells(m, 24).Interior.ColorIndex = 45
End If
If Cells(m, 24) >= 8 And Cells(m, 24) < 25 Then
Cells(m, 24).Interior.ColorIndex = 6
End If
If Cells(m, 24) >= 0 And Cells(m, 24) < 8 Then
Cells(m, 24).Interior.ColorIndex = 4
End If






    Next m

Display More

**Andy Pope** · Jul 2nd 2007

Re: "next Command" Not Going Past Row 19

What is the last cell in column I of sheet3?

rico355 · Jul 2nd 2007

Re: "next Command" Not Going Past Row 19

Yes i actually found out it was that, i was using a different worksheet to find the last cell. Thank you anyways Andy!

**Andy Pope** · Jul 2nd 2007

Re: "next Command" Not Going Past Row 19

No problem. Thanks for posting the reason and solution to your problem.

p45cal · Jul 2nd 2007

Re: "next Command" Not Going Past Row 19

Yes, put this in the Immediate pane of the VBE and see if it comes back with what you expect..
?sheet3.Name

Results might be a bit more predicatable with
mLastRow = Sheets("TheSheetsName AsOnTheTab").Cells(Rows.Count, "I").End(xlUp).Row

mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row

As for a better way to do it? Well here's a different way to do it, no vba:

This is for row 20, you can copy up/down:
In any column in row 20 place this formula:

=SUMPRODUCT(--({"Critical";"Serious";"Moderate";"Minor"}=I20),{10;8;5;1})*SUMPRODUCT(--({"High";"Medium";"Low"}=J20),{10;5;1})

Note that it refers to I20 and J20. This will give the same result of the multiplication as yours. Copy the formula up/down as required (and yes, you're right, this doesn't automatically find how far down to go).

Next format all those cells with a green background.

Finally in whichever cell you've chosen to put the formula you can add some conditional formatting. See the attached cf1 graphic. Alternatively, to be able to apply the conditional formatting to other cells, for example to colour the cells containing the words themselves see graphic cf2 attached which was looking at the value in cell, but could be applied to any cell, most usefully, in the same row.

Finally, without using any extra cells containing formulae, but requiring 2 small vlookup tables (2 columns each) anywhere in the workbook, even on another sheet, I created these and made them Named ranges, this one I called 'Grade':
High 10
Medium 5
Low 1

and this one 'Critical'
Critical 10
Serious 8
Moderate 5
Minor 1

then I changed the 'Formula is' formulae to:
Condtion 1
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=50
Condition 2
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=25
Condition 3
=(VLOOKUP($J20,Grade,2,0)*VLOOKUP($I20,Critical,2,0))>=8

not forgetting to have the default cell background set to green.
The conditional format still refers to the cells $I20 and $J20, which allows the conditional format to be copied and pasted (eg. with the format painter) to any row or column.

p45cal

Loop Not Completing All Loops

Participate now!

Share