Optimize Many ElseIf Statements in VBA

[danieljhb27]

I've got a table that is pulling data in from a query. It has work orders for six different departments on it. I use a large (and slow) macro with many ElseIf statements to determine which department each work order belongs to. I then use that cell in another macro that pastes the row into a sheet for each department.

The problem is this macro takes about 3 minutes to run. I know there's got to be a more efficient way to do it, but I have no idea how. It took me a while to get this code to work, and I'm thankful that it's actually working. I just would like some help optimizing it.

Any advice is much appreciated!

The code is below.

Sub Add_Departments()
Application.ScreenUpdating = False
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
For lrow = 4 To lastRow

'R
If Range("E" & lrow) <= 115 Then
Range("B" & lrow).Value = "R"

'M
ElseIf Range("E" & lrow) <> 103 And Range("E" & lrow) <> 104 And Range("E" & lrow) <> 113 And Range("F" & lrow) <> 115 And Range("E" & lrow) <> 601 And Range("E" & lrow) <> 454 And Range("E" & lrow) <> 482 And Range("E" & lrow) <> 473 And Range("E" & lrow) <> 481 And Range("D" & lrow) = "C20" And Range("C" & lrow) <> "Pots" And Range("C" & lrow) <> "Tots" And Range("C" & lrow) <> "Tolls" Then
Range("B" & lrow).Value = "M"

'MM
ElseIf Range("C" & lrow) = "Tots" And Range("D" & lrow) = "C20" And Range("E" & lrow) <> 103 And Range("E" & lrow) <> 104 And Range("E" & lrow) <> 113 And Range("E" & lrow) <> 115 And Range("E" & lrow) <> 302 And Range("E" & lrow) <> 454 And Range("E" & lrow) <> 473 And Range("E" & lrow) <> 481 And Range("E" & lrow) <> 482 And Range("E" & lrow) <> 601 Then
Range("B" & lrow).Value = "MM"

ElseIf Range("C" & lrow) = "Pots" And Range("D" & lrow) = "C20" And Range("E" & lrow) <> 103 And Range("E" & lrow) <> 104 And Range("E" & lrow) <> 113 And Range("E" & lrow) <> 115 And Range("E" & lrow) <> 302 And Range("E" & lrow) <> 454 And Range("E" & lrow) <> 473 And Range("E" & lrow) <> 481 And Range("E" & lrow) <> 482 And Range("E" & lrow) <> 601 Then
Range("B" & lrow).Value = "MM"

ElseIf Range("C" & lrow) = "Tolls" And Range("D" & lrow) = "C20" And Range("E" & lrow) <> 103 And Range("E" & lrow) <> 104 And Range("E" & lrow) <> 113 And Range("E" & lrow) <> 115 And Range("E" & lrow) <> 302 And Range("E" & lrow) <> 454 And Range("E" & lrow) <> 473 And Range("E" & lrow) <> 481 And Range("E" & lrow) <> 482 And Range("E" & lrow) <> 601 Then
Range("B" & lrow).Value = "MM"

'D
ElseIf Range("D" & lrow) = "C20" And Range("E" & lrow) >= 401 And Range("E" & lrow) <= 483 Then
Range("B" & lrow).Value = "D"

ElseIf Range("D" & lrow) = "C20" And Range("E" & lrow) = 204 Then
Range("B" & lrow).Value = "D"

'T
ElseIf Range("D" & lrow) = "T60" And Range("E" & lrow) <> 403 And Range("E" & lrow) <> 405 And Range("E" & lrow) <> 407 And Range("E" & lrow) <> 705 And Range("F" & lrow) <> 403 And Range("F" & lrow) <> 405 And Range("F" & lrow) <> 407 And Range("F" & lrow) <> 705 Then
Range("B" & lrow).Value = "T"


'P
ElseIf Range("D" & lrow) = "T60" And Range("E" & lrow) >= 403 And Range("E" & lrow) <= 407 Then
Range("B" & lrow).Value = "P"

ElseIf Range("D" & lrow) = "T60" And Range("F" & lrow) >= 403 And Range("F" & lrow) <= 407 Then
Range("B" & lrow).Value = "P"

End If
Next lrow
Application.ScreenUpdating = True
End Sub

 

[MUKESHY12390]

can you post all your criteria in simple word,I can try my best to provide solution if possible.

 

[Joe4]

The structure would look something like this:

Select Case Condition1
    Case Option 1
        Select Case Condition A
            Case Option A
                ...
            Case Option B
               ...
            Case Option C
               ...
    End Select
    Case Option 2
        Select Case Condition A
            Case Option A
                ...
            Case Option B
               ...
            Case Option C
               ...
    End Select
End Select

You can expand it out as far as you need to.