Use name range to determine data inserted in cell.

[Livin404]

Greetings, I have a macro that works great with limited criteria. However, I need it to use a name range if possible for there are about 2500 airport codes that I would need flagged. The listing would need to be NOT equal to the list because there are about 8000 that are equal to. My name range would be Airport_Codes. The current macro I would have to use would be:

Sub CA_Required()
    Dim i As Long
    For i = 1 To ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        Select Case Left(ActiveSheet.Cells(i, 1).Value, 3)
            Case "MHZ", "LHR", "STR", "RTA"
               
If ActiveSheet.Range("F" & i) = "" Then
 ActiveSheet.Range("F" & i) = "CUST & AG REQ"
      Else
ActiveSheet.Range("F" & i) = ActiveSheet.Range("F" & i) & " // CUST & AG REQ"
    End If
    End Select
Next i

End Sub

As you can see trying to list 8000 codes would be rather silly and a fruitless endeavor. Thank you so much.

 

[Zot]

You meant that if you use your code, you would have 2500 cases in Select Case? Even if you grouped to create a named range, you still end up with 2500 named range, right?

Am I understood this correctly? Probably, just tell helpers here what is your expected result.

 

[Livin404]

You meant that if you use your code, you would have 2500 cases in Select Case? Even if you grouped to create a named range, you still end up with 2500 named range, right?

Am I understood this correctly? Probably, just tell helpers here what is your expected result.

Yes you’re correct. Thank you for clarifying that.

 

[Zot]

Yes you’re correct. Thank you for clarifying that.

What else are you expecting in column A other than Airport Code? Other unwanted text or just blanks?

 

[Livin404]

What else are you expecting in column A other than Airport Code? Other unwanted text or just blanks?

Nothing else, if in column A one of those three letter codes doesn’t appear than those words will show up as described in the original question. Since I’m hoping to have a list I thought it would be easier to have a list of 2.5K list of codes as compared to 8K. If it doesn’t matter than we would have to change the code to reflect a <>.

 

[Livin404]

What else are you expecting in column A other than Airport Code? Other unwanted text or just blanks?

Basically I want the macro to recognize aircraft that would need Customs and Agricultural if coming from outside the Continental US. If the plane is not than nothing needs to happen. Thank you

 

[Zot]

Basically I want the macro to recognize aircraft that would need Customs and Agricultural if coming from outside the Continental US. If the plane is not than nothing needs to happen. Thank you

I think you just need to have a list plane prefix in a worksheet and get your code to compare to the list. Instead of using SELECT CASE, you can use FIND to check if the prefix is in the list. THe quicker way is to use Dictionary since it has function that can tell you if the prefix existed in the Dictionary. You can also use array but need custom function that involve looping the array but still way faster than looping the sheet.

These are the best options I can think of

 

[Livin404]

This is what I ended up using, and it works fine. Thank you for those who helped.

Sub CA_Required()
    Dim rng As Range
    Dim cel As Range
    Dim i As Long
    Dim m As Long
    Application.ScreenUpdating = False
    Set rng = Worksheets("List").Range("A1:A2500")
    m = Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To m
        Set cel = rng.Find(What:=Left(Range("A" & i).Value, 3), LookAt:=xlWhole)
        If Not cel Is Nothing Then
            If Range("F" & i) = "" Then
                Range("F" & i) = "CUST & AG REQ"
            Else
                Range("F" & i) = Range("F" & i) & " // CUST & AG REQ"
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Zot]

I was thinking that the plane can be group just by looking at prefix. So, you have found the solution.