Keeping the 91' error in VBA Excel

Question

I've a 91 error on my code, nothing seems to work.

The next code is for identify each line wich contains LOW, MEDIUM, HIGH according to another table, the problem is that my data doesn't contain 'LOW' by the time, I guess I need to add an IF so the code can start searching MEDIUM value, but I don't know how to do it.

Private Sub CommandButton1_Click()

Dim DataCalc_LOW As Integer
Dim DataCalc_MEDIUM As Integer
Dim DataCalc_HIGH As Integer

DataToCalc_LOW = Worksheets("Random Generator").Range("AL16").Value
DataToCalc_MEDIUM = Worksheets("Random Generator").Range("AL17").Value
DataToCalc_HIGH = Worksheets("Random Generator").Range("AL18").Value

Dim x As Integer
Dim y As Integer

Range("I14").Select
Range(Selection, Selection.End(xlDown)).Select

'x = 0

Do
x = x + 1
Selection.Find(What:="LOW", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,                 
SearchFormat:=False).Activate
With ActiveCell
.Interior.ColorIndex = 44
End With
ActiveCell.Select
Selection.Offset(0, 0).Select
Range(Selection, Selection.End(xlDown)).Select

Loop Until x = DataToCalc_LOW

Range("I14").Select
Range(Selection, Selection.End(xlDown)).Select

'y = 0

Do
y = y + 1
Selection.Find(What:="MEDIUM", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,                 
SearchFormat:=False).Activate
With ActiveCell
.Interior.ColorIndex = 44
End With
ActiveCell.Select
Selection.Offset(0, 0).Select
Range(Selection, Selection.End(xlDown)).Select

Loop Until y = DataToCalc_MEDIUM

Range("I14").Select
Range(Selection, Selection.End(xlDown)).Select

'Z = 0
Do
Z = Z + 1
Selection.Find(What:="HIGH", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,         
SearchFormat:=False).Activate
With ActiveCell
.Interior.ColorIndex = 44
End With
ActiveCell.Select
Selection.Offset(0, 0).Select
Range(Selection, Selection.End(xlDown)).Select

Loop Until Z = DataToCalc_HIGH

For i = 15 To 5000
cuenta = WorksheetFunction.CountIf(Range("F15:F500"), Cells(i, "F"))
If cuenta = 1 Then
    Cells(i, "I").Interior.ColorIndex = 44
End If
Next
'ActiveSheet.Protect Password:="QC"

End Sub

score 0 · Answer 1 · answered Aug 07 '17 at 20:14

Your loops Do...Loop Until (condition) are running at least once and the condition is checked at the end of the loop. You can use Do Until (condition)...Loop loop instead. In that case condition is checked at the beginning and if it's true already, then loop will not run.

Keeping the 91' error in VBA Excel

1 Answers1