CODEWITHSUNDEEP
vba
Kumar
Kumar

Reputation:

Excel IF -Then-Else condition

Kindly assist to run the below Excel macro. I think i'm missing some thing. Logic not working.

Sub RiskGrade()

Dim Value1 As String

Sheets("Sheet1").Select
If (Cells(10, 2) = "BX") Then Cells(12, 4) = -8
Exit Sub

ElseIf (Cells(10, 2) = "GX") Then Cells(12, 4) = -7
Exit Sub

Else
    For i = 12 To 14

        Value1 = Right(Trim(Sheet1.Cells(i, 2)), 1)
        'MsgBox Value1
        Select Case Value1
            Case "W"
                Cells(i, 4) = 1
            Case "H", "S", "R", "F", "G"
                Cells(i, 4) = 2
            Case "D"
                Cells(i, 4) = 3
            Case "C"
                Cells(i, 4) = 4
            Case "B"
                Cells(i, 4) = 5
            Case "A"
                Cells(i, 4) = 6
            Case "*"
                Cells(i, 4) = 7
            Case "M"
                Cells(i, 4) = 8
            Case "E"
                Cells(i, 4) = 9
            Case OTHER
                Cells(i, 4) = -9
        End Select
        Exit For
    Next i
End If

End Sub

Upvotes: 0

Views: 5742

Answers (4)

Qbik
Qbik

Reputation: 6147

You should use OPTION EXPLICIT, because it helps to spot errors when typos are applied to keywords.

In current shape you specified unexpected behaviour by using Case OTHER which should be Case Else, there is no keyword OTHER in VBA so OTHER is interpreted as variable of Variant datatype, but Case Else would be interpreted properly as "in other case do ...".

Also using Exit For after Select stops macro execution on i=12, 13 and 14 are then not applied.

Option Explicit
Sub RiskGrade()

Dim Value1 As String

Sheets("Sheet1").Select

If (Cells(10, 2) = "BX") Then 
Cells(12, 4) = -8
Exit Sub

ElseIf (Cells(10, 2) = "GX") Then 
Cells(12, 4) = -7
Exit Sub

Else
    For i = 12 To 14

        Value1 = Right(Trim(Sheet1.Cells(i, 2)), 1)
        Debug.Print Value1 'CTRL+G for immediate window

        Select Case Value1
            Case "W"
                Cells(i, 4) = 1
            Case "H", "S", "R", "F", "G"
                Cells(i, 4) = 2
            Case "D"
                Cells(i, 4) = 3
            Case "C"
                Cells(i, 4) = 4
            Case "B"
                Cells(i, 4) = 5
            Case "A"
                Cells(i, 4) = 6
            Case "*" 'I don't know what behaviour in will cause, does it spot '*' only, or any char
                Cells(i, 4) = 7
            Case "M"
                Cells(i, 4) = 8
            Case "E"
                Cells(i, 4) = 9
            Case Other ' not OTHER
                Cells(i, 4) = -9
        End Select
        'Exit For ' - why exit after first iteration - guess it's unnecessary procedure ?
    Next i
End If

End Sub

Upvotes: 0

user3023582
user3023582

Reputation: 44

If you remove both the "exit sub" it should work. With the "exit sub" it seems that you are closing your if without "end if"

Upvotes: -1

SeanC
SeanC

Reputation: 15923

  1. Case OTHER should be Case Else
  2. The Exit For will terminate the loop after the first time through, so that needs to be removed, or changed to where you want the processing to abort

  3. The If ... Then should be multi-line, or singleline.

    • Multi-Line:
      If (Cells(10, 2) = "BX") Then
      Cells(12, 4) = -8
      Exit Sub
      ElseIf ...

    • Single Line:
      If (Cells(10, 2) = "BX") Then Cells(12, 4) = -8 : Exit Sub
      ElseIf ...
      (note use of : to separate commands in same If statement)

Upvotes: 3

Jay Wick
Jay Wick

Reputation: 13707

You've started an IF statement and in the same line added an action, which means it's not going to expect an END IF or ELSE after that.

In VB

IF condition THEN action

is a single IF statement.

Consider

If (Cells(10, 2) = "BX") Then
  Cells(12, 4) = -8
  Exit Sub
ElseIf (Cells(10, 2) = "GX") Then
  Cells(12, 4) = -7
  Exit Sub
...

Upvotes: 9

Related Questions