Reputation: 11
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
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
Reputation: 71247
The syntax for If
allows two variants:
"Inline"
If {bool-expression} Then {do something}
"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 (hadn't read where ProdE
is greater than or equal to 1?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
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
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
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