CODEWITHSUNDEEP
excelvbaif-statement
RG1of2
RG1of2

Reputation: 53

Cascading if statement not working

I am getting the compile error "Else without If" in the following part of my code. What's the issue and how do I fix it?

    If InStr(1, Worksheets("Raw data").Cells(i, 3), "1M") Then Call RowCopyPaste("AS-001", i, counter1M)
    counter1M = counter1M + 1

    ElseIf InStr(1, Worksheets("Raw data").Cells(i, 3), "2M") Then Call RowCopyPaste("AS-001", i, counter2M)
    counter2M = counter2M + 1

    ElseIf InStr(1, Worksheets("Raw data").Cells(i, 3), "3M") Then Call RowCopyPaste("AS-001", i, counter3M)
    counter3M = counter3M + 1

    End If

Upvotes: 2

Views: 98

Answers (2)

Parfait
Parfait

Reputation: 107652

Alternatively, you can replace the nested If with Select Case and even the Instr() with Like operator:

 Select Case True

    Case Worksheets("RawData").Cells(1, 1) Like "*1M*"
       Call RowCopyPaste("AS-001", i, counter1M)
       counter1M = counter1M + 1

    Case Worksheets("RawData").Cells(1, 1) Like "*2M*"
       Call RowCopyPaste("AS-001", i, counter2M)
       counter2M = counter2M + 1

    Case Worksheets("RawData").Cells(1, 1) Like "*3M*"
       Call RowCopyPaste("AS-001", i, counter3M)
       counter3M = counter3M + 1

    Case Else
       '...

End Select

Upvotes: 2

user4039065
user4039065

Reputation:

An If comparison can live on the same line as a single command but not as you are doing it. Your two-commands-per-condition should be more like the following.

If InStr(1, Worksheets("Raw data").Cells(i, 3), "1M") Then
    Call RowCopyPaste("AS-001", i, counter1M)
    counter1M = counter1M + 1
ElseIf InStr(1, Worksheets("Raw data").Cells(i, 3), "2M") Then
    Call RowCopyPaste("AS-001", i, counter2M)
    counter2M = counter2M + 1
ElseIf InStr(1, Worksheets("Raw data").Cells(i, 3), "3M") Then
    Call RowCopyPaste("AS-001", i, counter3M)
    counter3M = counter3M + 1
End If

Something like the following could live by itself but not with other ElseIf commands.

If InStr(1, Worksheets("Raw data").Cells(i, 3), "1M") Then Call RowCopyPaste("AS-001", i, counter1M)

You might want to try this as a Select Case statement if you have any more conditions to cover.

Upvotes: 2

Related Questions