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]

apart from these formula , how many formula workbook has?

 

[danieljhb27]

apart from these formula , how many formula workbook has?

There are currently no formulas in any of the worksheets.

I have 4 other macros that are all relatively quick.

The first clears the column where the departments are added and then refreshes the query.

The second sorts the table after the departments are added.

The third clears all of the department sheets.

The fourth copies and pastes the rows into the department sheets.

 

[danieljhb27]

Bump.

 

[SpillerBD]

Look into Case.Select instead

 

[danieljhb27]

Look into Case.Select instead

I'm looking into this right now. Is using Case.Select the best option when there is multiple criteria from different ranges that need to be evaluated? If so, how would I go about writing a formula that checked different criteria in different ranges?

 

[Joe4]

I would probably look at nesting Case statements.
I would recommend sketching it out first, in a hierarchical chart on paper first, then create it.

Here are some links on Case statements:
https://www.techonthenet.com/excel/formulas/case.php
https://www.mrexcel.com/forum/excel-questions/761130-select-case-multiple-condition-vba.html

 

[SpillerBD]

See MSDN https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/using-select-case-statements
However the version you probably want revolves around

SELECT CASE TRUE

Then each Case statement is followed by an evaluation and if TRUE, that is the one to be executed. If you know the frequency of different cases, try and order those statements by highest frequency first. (Same for If...Else)

I used John Walkenbach's VBA book to pick up this use.

 

[MUKESHY12390]

I'm glad you find solution on your own.

 

[danieljhb27]

I'm glad you find solution on your own.

Unfortunately I haven't came up with a solution. I've been reading different material on the subject for a few hours and I'm just not understanding how a nested Case.Select statement would look.

Thanks for everybody's advice, but it looks like I'm just going to keep running the same code I started with.