CODEWITHSUNDEEP
vbaexcel
leon Hill
leon Hill

Reputation: 93

Multiple if statements in a loop vba

I am trying to run a simple if statement but can not get it to run for me without getting an error. Basically, I am trying to have multiple if statements within a loop, im guessing i have a minor error but cannot spot it, maybe with the else statements. Any help is appreciated 

Sub ex13()

Dim rgtimetable As Range, rgR As Range, counter As Integer
Dim counter1 As Integer, counter2 As Integer, counter3 As Integer

Set rgtimetable = Range("timetable")
For Each rgR In rgtimetable
    rgR.Activate
    If classyear(ActiveCell.Value) = 0 Then counter = counter + 1   ' classyear is a function i am calling from above

    Else
    If classyear(ActiveCell.Value) = 1 Then counter = counter1 + 1

    Else
    If classyear(ActiveCell.Value) = 2 Then counter = counter2 + 1

    Else
    If classyear(ActiveCell.Value) = 3 Then counter = counter3 + 1

Next rgR

MsgBox counter
MsgBox counter1
MsgBox counter2
MsgBox counter3

End Sub

Upvotes: 0

Views: 13539

Answers (1)

SteveES
SteveES

Reputation: 554

In VBA there are several ways to write an If statement:

If [some condition] Then [Do something]

Or

If [some condition] Then [Do something] Else [Do something else]

Or

If [some condition] Then
   [Do something]
End If

Or

If [some condition] Then
   [Do something]
Else
   [Do something else]
End If

Or, finally

If [some condition] Then
   [Do something]
ElseIf [some other condition] Then
   [Do something different]
Else
   [Do something else]
End If

In your code your If statements are all on one line and therefore don't need a corresponding End If, but also can't take a corresponding Else statement on a following line. If you want to use an Else or an ElseIf, you must use the final If statement block pattern and close the If block with a corresponding EndIf.

In your case, as you are always testing the same thing (classyear(ActiveCell.Value)), I would advise taking advantage of the Select Case construction, which will shorten your code.

Select Case classyear(ActiveCell.Value)
Case 0
   counter = counter + 1   ' classyear is a function i am calling from above

Case 1
   counter = counter1 + 1

Case 2
   counter = counter2 + 1

Case 3
   counter = counter3 + 1

Case Else
   'Oops, this shouldn't happen, but handle the error anyway

End Select

Upvotes: 5

Related Questions