SIMPLIFY CODE - 'CASE' Code needs simplification

[Sphinx404]

Hello VBA experts,

I need to clean up this code. The data set is only 1947 rows and this takes over 3 minutes to run on a pretty fast computer. I know there are many ways to run a process, so I am hoping someone can look at this code and tell/show me what needs to be changed in order for it to run more efficiently:

Highlighted in RED is the range I had previously setup to account for all cells to the bottom of the data set (and where I think my problem in efficiency lies), but I think this is looping through the entire range rather than looking through the row itself. The process should work from left to right... not necessarily processing entire range, then finding what's true, then returning a value. I'm not savvy with VBA so I need a code giant to simply if possible. Thank you guys!

Sub Step10()
'THIS SUB: if the first 5 characters in mycell (Column J) begin with [*****] and
'the value of myrange (column A) represent anything LIKE *** then return TRUE


ActiveWorkbook.Sheets("HazShipper").Select


Dim mycell As Range
Dim myrange As Range


Application.ScreenUpdating = False
Application.DisplayAlerts = False


[B][COLOR=#ff0000]For Each mycell In Range("J2", Range("J" & Rows.Count).End(xlUp))[/COLOR][/B]
[B][COLOR=#ff0000]For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))[/COLOR][/B]


Select Case UCase(Left(mycell.Value, 5))
    Case "MINNE"
        If myrange.Value Like "*MSP*" Then
            mycell.Offset(, 13).Value = "True"
        End If
    Case "ST.PA"
        If myrange.Value Like "*MSP*" Then
            mycell.Offset(, 13).Value = "True"
        End If
    Case "ATLAN"
        If myrange.Value Like "AT*" Then
            mycell.Offset(, 13).Value = "True"
        End If
    Case "DETRO"
        If myrange.Value Like "DTW*" Then
            mycell.Offset(, 13).Value = "True"
        End If
    Case Else
        mycell.Offset(, 13).Value = "False"
End Select


'****NEED**** CASE ELSE to account for those that do not match and return "FALSE"


Next myrange
Next mycell


Application.ScreenUpdating = True
Application.DisplayAlerts = True

'organizes returned data
Range("W1").Select
        ActiveCell.FormulaR1C1 = "Departure Airport Macth Column A?"
        Selection.Font.Bold = True
    Columns("W:W").Select
    Columns("W:W").EntireColumn.AutoFit
    With Selection
        .HorizontalAlignment = xlCenter
    End With


End Sub

 

[JLGWhiz]

The code you have in red loops through each cell for 2 columns, A and J. What do you want to check and how is it laid out on the worksheet in terms of columns and rows?

 

[JLGWhiz]

I think you can do what the code appears to be doing with one loop.

For Each myrange In Range("A2", Range("A" & Rows.Count).End(xlUp))
    Select Case UCase(Left(myrange.Offset(, 9), 5))
        Case "MINNE"
            If myrange.Value Like "*MSP*" Then
                myrange.Offset(, 22) = "True"
            End If
        Case "ST>PA"
            If myrange.Value Like "*MSP*" Then
                myrange.Offset(, 22) = "True"
            End If
        Case "ATLAN"
            If myrange.Value Like "AT*" Then
                myrange.Offset(, 22) = "True"
            End If.
        Case "DETRO"
            If myrange.Value Like "DTW*" Then
                myrange.Offset(, 22) = "True"
            End If
        Case Else
            myrange.Offset(, 22) = "False"
    End Select
Next

I didn't test this, so look out for typos.

 

[Sphinx404]

@JLGWhiz

Nice fix! Worked great. Honestly, I was skeptical at first, but I've run through it a couple different time and it seems to work just like the initial code, just better.

Thank you!

 

[JLGWhiz]

@JLGWhiz

Nice fix! Worked great. Honestly, I was skeptical at first, but I've run through it a couple different time and it seems to work just like the initial code, just better.

Thank you!

You are welcome,
regards, JLG