CODEWITHSUNDEEP
vbaexcel
Sanicho3
Sanicho3

Reputation: 11

End If without Block If error VBA

I wrote this code to try and assign a value to a variable based on the value of another variable generated using vba's Rnd() function and if statements but for some reason its giving me the "end if without block if error." This is just a portion of the code, I iterate this process 5 times for the 5 different products and do 10000 iterations of the number generators aggregating the results. Initially I tried it this way nesting everything, but when that didnt work I tried doing single if statements and same deal. Any help with this would be awesome.

For i = 0 To 10000

ProdE = Rnd()
ProdF = Rnd()
ProdG = Rnd()
ProdH = Rnd()
ProdI = Rnd()

If ProdE <= 0.1 Then DaysLateE = 2
If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
If 0.9 < ProdE <= 1 Then DaysLateE = 11
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If


TotalDaysLateE = DaysLateE + 8
SumDaysLateE = SumDaysLateE + TotalDaysLateE

If TotalDaysLateE > 15 Then CountE = CountE + 1
End If

Upvotes: 1

Views: 3169

Answers (5)

grug.0
grug.0

Reputation: 355

Thinking about your branching and conditional path, and for 10000 iterations, I'd suggest to just Bifurcate your If... Then statement. Better yet, use this in conjunction with two smaller Case... Select for an easily readable combination of all the suggestions. FASTER!

If ProdE <= 0.5 Then
    If ProdE <= 0.1 Then DaysLateE = 2
    If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
    If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
    If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
    If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
Else
    If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
    If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
    If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
    If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
    If 0.9 < ProdE <= 1 Then DaysLateE = 11
End If

Upvotes: 0

Mathieu Guindon
Mathieu Guindon

Reputation: 71247

The syntax for If allows two variants:

  1. "Inline"

    If {bool-expression} Then {do something}

  2. "Block"

    If {bool-expression} Then
    {do something}
End If

An End If token is illegal when you're using the "inline" syntax.

So this (i.e. removing the End If tokens) makes your code compilable again:

If ProdE <= 0.1 Then DaysLateE = 2
If 0.1 < ProdE <= 0.2 Then DaysLateE = 3
If 0.2 < ProdE <= 0.3 Then DaysLateE = 4
If 0.3 < ProdE <= 0.4 Then DaysLateE = 5
If 0.4 < ProdE <= 0.5 Then DaysLateE = 6
If 0.5 < ProdE <= 0.6 Then DaysLateE = 7
If 0.6 < ProdE <= 0.7 Then DaysLateE = 8
If 0.7 < ProdE <= 0.8 Then DaysLateE = 9
If 0.8 < ProdE <= 0.9 Then DaysLateE = 10
If 0.9 < ProdE <= 1 Then DaysLateE = 11

However, as @Rohan K suggested, a better option would be to use a Select Case construct, because right now, all these conditions are evaluated all the time - with a Select Case block, execution would exit the Select block after finding a matching condition, and as a bonus you gain readabiilty:

Select Case ProdE
    Case Is <= 0.1
        DaysLateE = 2
    Case Is <= 0.2
        DaysLateE = 3
    Case Is <= 0.3
        DaysLateE = 4
    Case Is <= 0.4
        DaysLateE = 5
    Case Is <= 0.5
        DaysLateE = 6
    Case Is <= 0.6
        DaysLateE = 7
    Case Is <= 0.7
        DaysLateE = 8
    Case Is <= 0.8
        DaysLateE = 9
    Case Is <= 0.9
        DaysLateE = 10
    Case Is <= 1
        DaysLateE = 11
    Case Else
        'DaysLateE = ??
End Select

So, what happens when ProdE is greater than or equal to 1? (hadn't read where ProdE came from, nevermind) It seems there's a straight linear relationship between the value of ProdE and DaysLateE - you could try to come up with a formula to calculate it instead.

This probably isn't perfect, but comes pretty close:

DaysLateE = Int(ProdE * 10 - 0.000000000001) + 2

And then you don't need If or Select blocks.

Upvotes: 2

Stupid_Intern
Stupid_Intern

Reputation: 3460

Try this: I would suggest Use Select Case incases like this

    For i = 0 To 10000

ProdE = Rnd()
ProdF = Rnd()
ProdG = Rnd()
ProdH = Rnd()
ProdI = Rnd()

If ProdE <= 0.1 Then
DaysLateE = 2
End If

If 0.1 < ProdE And ProdE <= 0.2 Then
DaysLateE = 3
End If

If 0.2 < ProdE And ProdE <= 0.3 Then
DaysLateE = 4
End If

If 0.3 < ProdE And ProdE <= 0.4 Then
DaysLateE = 5
End If

If 0.4 < ProdE And ProdE <= 0.5 Then
DaysLateE = 6
End If

If 0.5 < ProdE And ProdE <= 0.6 Then
DaysLateE = 7
End If

If 0.6 < ProdE And ProdE <= 0.7 Then
DaysLateE = 8
End If

If 0.7 < ProdE And ProdE <= 0.8 Then
DaysLateE = 9
End If

If 0.8 < ProdE And ProdE <= 0.9 Then
DaysLateE = 10
End If

If 0.9 < ProdE And ProdE <= 1 Then
DaysLateE = 11
End If


TotalDaysLateE = DaysLateE + 8
SumDaysLateE = SumDaysLateE + TotalDaysLateE

If TotalDaysLateE > 15 Then
CountE = CountE + 1
End If

Upvotes: 1

Zaider
Zaider

Reputation: 2013

The problem is your final If statement there. It is completely valid syntax to have an If statement all on one line without an End If. So when you put that End If there it is expecting an If statement with lines after it.

These two would be valid without an error

If TotalDaysLate > 15 then CountE = CountE + 1

Or

If TotalDaysLate > 15 Then 
    CountE = CountE + 1
End If

Upvotes: 1

Xavier Navarro
Xavier Navarro

Reputation: 36

When you use single line ifs you don't need to write the End if. Check this: https://msdn.microsoft.com/en-us/library/office/gg251599.aspx

Upvotes: 0

Related Questions