Solve Block If Without End If Problem

Printable View

06-12-2013, 08:29 AM
jffryjsphbyn

Solve Block If Without End If Problem

Hello everyone,

Can someone help me out to fix and debug this code.

Sub SLA_ID()
Dim Classification As String, class As String, volume As Integer, netWDay As Integer, classification2 As Integer

Classification = Range("F2:F500").Value
volume = Range("J2:J500").Value
netWDay = Range("AH2:AH500").Value

If ((Classification = "Reorganization" & netWDay <= 5)) Then
classification2 = "Within SLA"

Else
Classification = "Beyond SLA"

If ((Classification = "CR" & netWDay = 1)) Then
classification2 = "Within SLA"

Else
Classification = "Beyond SLA"

If ((Classification = "Other Forms" & netWDay <= 1)) Then
classification2 = "Within SLA"

Else
Classification = "Beyond SLA"

If ((Classification = "MMR" & volume <= 30)) Then
class = "MMR_Low"

Else
Classification = "MMR_High"

If ((class = "MMR_Low" & netWDay <= 2)) Then
classification2 = "Within SLA"

Else
Classification = "Beyond SLA"

If ((class = "MMR_High" & netWDay <= 5)) Then
Classification = "Within SLA"
Else
Classification = "Beyond SLA"

Range("AM2:AM500").Value = class
Range("AL2:AL500").Value = classification2

End If

End Sub

The error is Block if without end if, and if I put a total number of end if at the end with the total number of If statement is my codes, the error is block if without end if. and if ever I remove it I usually have a problem with Runtime error '13': Type Mismatch.

Thank you in advance.

-Jeff
06-12-2013, 10:13 AM
Excel Fox

is this what you wanted

Code:

Sub SLA_ID() Dim Classification As String, class As String, volume As Integer, netWDay As Integer, classification2 As Integer Classification = Range("F2:F500").Value volume = Range("J2:J500").Value netWDay = Range("AH2:AH500").Value If ((Classification = "Reorganization" & netWDay <= 5)) Then classification2 = "Within SLA" Else Classification = "Beyond SLA" End If If ((Classification = "CR" & netWDay = 1)) Then classification2 = "Within SLA" Else Classification = "Beyond SLA" End If If ((Classification = "Other Forms" & netWDay <= 1)) Then classification2 = "Within SLA" Else Classification = "Beyond SLA" End If If ((Classification = "MMR" & volume <= 30)) Then class = "MMR_Low" Else Classification = "MMR_High" End If If ((class = "MMR_Low" & netWDay <= 2)) Then classification2 = "Within SLA" Else Classification = "Beyond SLA" End If If ((class = "MMR_High" & netWDay <= 5)) Then Classification = "Within SLA" Else Classification = "Beyond SLA" Range("AM2:AM500").Value = class Range("AL2:AL500").Value = classification2 End If End Sub
06-12-2013, 10:45 AM
jffryjsphbyn

Runtime Error '13': Type mismatch is the problem. I don't know why. I have already double checked the codes. :( Please help! :)_
06-12-2013, 11:06 AM
Rick Rothstein

You have a few things wrong with your code and, because we do not know what the code is supposed to be doing, it is impossible to fix perfectly for you... but we can guide you to a solution. First off, Excel Fox has shown you where to place all of the required "End If" statements. Mind you, his placements are only a guess based on the apparent structure your code seemed to possess, but I think he got it right EXCEPT possibly for the placement of the last "End If". This is what Excel Fox suggested for it...

Code:

If ((class = "MMR_High" & netWDay <= 5)) Then Classification = "Within SLA" Else Classification = "Beyond SLA" Range("AM2:AM500").Value = class Range("AL2:AL500").Value = classification2 End If End Sub

It is impossible for us to tell positively where the lines of code I highlighted in red are supposed to be located, but I think this is a more logical arrangement for them give the code that proceed it...

Code:

If ((class = "MMR_High" & netWDay <= 5)) Then Classification = "Within SLA" Else Classification = "Beyond SLA" End If Range("AM2:AM500").Value = class Range("AL2:AL500").Value = classification2 End Sub

Unfortunately, that is not the only thing I see wrong with your code. First, you declared your 'classification2' variable as Integer, but then within your code you assign "Within SLA" to it... since "Within SLA" is text, you should declare the variable as String, not Integer. Next, I think everyone of your If..Then statement lines are incorrect. You wrote something similar to this for each one of them...

Code:

If ((Classification = "Reorganization" & netWDay <= 5)) Then

I think you meant the & sign to be the logical operator And. If I am right, you need to change every one of your If..Then statements so that it looks like this...

Code:

If (Classification = "Reorganization") And (netWDay <= 5) Then

Now, about the values being assigned to your variables. You assign values to the 'Classification' variable, but then you never assign the 'Classification' variable to anything meaning the values you assigned to it are lost with being used when your code ends. You do assign the 'class' variable to the range AM2:AM500 at the end, but only assign a value to it once within one of the If..Then statement... if that If..Then statement is not True during the running of your code, then nothing gets assigned to AM2:AM500 which, might possibly be what you intended, but my gut tells me that no, that is not what you ultimately wanted. As I said at the beginning, without knowing the actual intent of your code, it is impossible to say which of what I pointed out above really applies or not, but I have offered you my observation so you can take a more critical look at what you are doing inside your code.