1

I am getting the error "else without if" in the code below. Can someone please help?

For I = 2 To LR5
   If (Range("Ae" & I).Value = 5 & Range("x" & I).Value = 2 & Range("y" & I).Value = 2) Then Range("ah" & I).Value = "30000"
   ElseIf (Range("Ae" & I).Value = 5 & Range("x" & I).Value = 4 & Range("y" & I).Value = 4) Then Range("ah" & I).Value = "60000"
   ElseIf Range("Ae" & I).Value = "29" Then Range("ah" & I).Value = "10000"
   ElseIf Range("Ae" & I).Value = "30" & Range("x" & I).Value = "4" Then Range("ah" & I).Value = "80000"
   ElseIf Range("Ae" & I).Value = "30" & Range("x" & I).Value = "2" Then Range("ah" & I).Value = "50000"
   ElseIf Range("Ae" & I).Value = "17" & Range("x" & I).Value = "2" Then Range("ah" & I).Value = "60000"
   ElseIf Range("E" & I).Value = "RRUS11" Then Range("AH" & I).Value = "80000"
   ElseIf Range("E" & I).Value = "RRUS11+RRUSA2" Then Range("AH" & I).Value = "80000"
   ElseIf Range("E" & I).Value = "RRUS12" Then Range("AH" & I).Value = "120000"
   End If
Next
41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
Amit
  • 327
  • 2
  • 11

2 Answers2

2

TL;DR: You need to move everything after Then to a new line.

In VBA, one-line-If-statements have the following characteristics:

  • They don't have End If, ever.
  • They must be written entirely as one line.1

For example:

If someCondition Then DoSomething
If someCondition Then DoSomething Else DomeSomethingElse
If someCondition Then DoSomething(x) Else If anotherCondition Then DoSomething(y) Else DoSomething(z)

However, one-line-if should be used exclusively for short statements. It's generally not a good idea to use it when you have Else (let alone Else If). So, in the code block above, the second and third examples are not recommended. Instead, you should write your If statements like this:

If someCondition Then
    DoSomething (x)
Else If anotherCondition Then
    DoSomething (y)
Else
    DoSomething (z)
End If

1 You can, of course, break statements into multiple lines using the _ though. See this for more.

41686d6564 stands w. Palestine
  • 19,168
  • 12
  • 41
  • 79
0

in a boolean expression you want to use AND instead of &

furthermore in such a convoluted case a Select Case block is much more readable:

For I = 2 To LR5
    Select Case True
        Case Range("Ae" & I).Value = 5 And Range("x" & I).Value = 2 And Range("y" & I).Value = 2
            Range("ah" & I).Value = "30000"
        Case Range("Ae" & I).Value = 5 And Range("x" & I).Value = 4 And Range("y" & I).Value = 4
            Range("ah" & I).Value = "60000"
        Case Range("Ae" & I).Value = "29"
            Range("ah" & I).Value = "10000"
        Case Range("Ae" & I).Value = "30" And Range("x" & I).Value = "4"
            Range("ah" & I).Value = "80000"
        Case Range("Ae" & I).Value = "30" And Range("x" & I).Value = "2"
            Range("ah" & I).Value = "50000"
        Case Range("Ae" & I).Value = "17" And Range("x" & I).Value = "2"
            Range("ah" & I).Value = "60000"
        Case Range("E" & I).Value = "RRUS11"
            Range("AH" & I).Value = "80000"
        Case Range("E" & I).Value = "RRUS11+RRUSA2"
            Range("AH" & I).Value = "80000"
        Case Range("E" & I).Value = "RRUS12"
            Range("AH" & I).Value = "120000"
        Case Else
            ' put code to handle the case when no preceeding conditions are met
    End Select
Next

and here's another way of translating the same If Then Else If of yours into a Select Case block:

For I = 2 To LR5

    Select Case Range("Ae" & I).Value

        Case 5
            Select Case True
                Case Range("x" & I).Value = 2 And Range("y" & I).Value = 2
                    Range("ah" & I).Value = "30000"
                Case Range("x" & I).Value = 4 And Range("y" & I).Value = 4
                    Range("ah" & I).Value = "60000"
            End Select

        Case "29"
            Range("ah" & I).Value = "10000"

        Case "30"
            Select Case Range("x" & I).Value
                Case "4"
                    Range("ah" & I).Value = "80000"
                Case "2"
                    Range("ah" & I).Value = "50000"
            End Select
        Case "17"
            If Range("x" & I).Value = "2" Then Range("ah" & I).Value = "60000"

        Case Else
            Select Case Range("E" & I).Value
                csse "RRUS11", "RRUS11+RRUSA2"
                    Range("AH" & I).Value = "80000"

                Case "RRUS12"
                    Range("AH" & I).Value = "120000"

                Case Else
                    ' you may want to put code to handle the case when no preceeding condition is met
            End Select

    End Select

Next
DisplayName
  • 13,283
  • 2
  • 11
  • 19