VBA String Range Detection

[dmelt253]

Hello, 1st Time Poster Here...
I work in a warehouse that assigns certain areas to individuals so that they are responsible for their own sections. The way the bins (Warehouse locations) are named are easily sortable in Excel so that part is good. I would like to create a function that evaluates a bin name and assigns an area owner's name based on which range they fall into. This is a table that breaks down the ranges (Min & Max String Values) so that if I run the function and it is >= the Min and <= the Max it should assign that person's name:

Min	Max	Area Owner
F38-01	F41-20	Matt
R01-01A	R01-16H	Jim
R01-17A	R01-32H	Dio
R01-33A	R01-68H	Matt
R02-01A	R03-68H	Bryan
R04-01A	R05-68H	Tom
S01-01A	S02-04E	Matt
Y01-01	Y01-10	Matt

Right now I just want the evaluation part to run and bring up a message box with the Area Owner's name. I'm fairly new to VBA but I gave it a try and came up with an error. Can someone please take a look at my function and let me know how far off I am?
Thank you!

Public Function AreaOwnerSort(bin1)
Dim bin1 As String
Dim aOwner As String

Const floorMin As String = "F38-01"
Const floorMax As String = "F41-20"
Const rack1aMin As String = "R01-01A"
Const rack1aMax As String = "R01-16H"
Const rack1bMin As String = "R01-17A"
Const rack1bMax As String = "R01-32H"
Const rack1cMin As String = "R01-33A"
Const rack1cMax As String = "R01-68H"
Const rack23Min As String = "R02-01A"
Const rack23Max As String = "R03-68H"
Const rack45Min As String = "R04-01A"
Const rack45Max As String = "R05-68H"
Const secureMin As String = "S01-01A"
Const secureMax As String = "S02-04E"
Const yardMin As String = "Y01-01"
Const yardMax As String = "Y01-10"
If (bin1 >= floorMin) And (bin1 <= floorMax) Then
    aOwner = "Matt"
    ElseIf (bin1 >= rack1aMin) And (bin1 <= rack1aMax) Then
    aOwner = "Jim"
    ElseIf (bin1 >= rack1bMin) And (bin1 <= rack1bMax) Then
    aOwner = "Dio"
    ElseIf (bin1 >= rack1cMin) And (bin1 <= rack1cMax) Then
    aOwner = "Matt"
    ElseIf (bin1 >= rack23Min) And (bin1 <= rack23Max) Then
    aOwner = "Bryan"
    ElseIf (bin1 >= rack45Min) And (bin1 <= rack45Max) Then
    aOwner = "Tom"
    ElseIf (bin1 >= secureMin) And (bin1 <= secureMax) Then
    aOwner = "Matt"
    ElseIf (bin1 >= yardMin) And (bin1 <= yardMax) Then
    aOwner = "Matt"
End If
MsgBox Str(aOwner)
End Function

Here is a small sample of a list of bins so you can see what I'm working with:
F38-01
F38-01
F38-04
F38-04
F38-10
F38-10
F41-07
F41-07
R01-01A
R01-01A
R01-01B-01
R01-01B-01
R01-01B-02
R01-01B-02
R01-02B-01
R01-02B-01
R01-03B-01
R01-03B-01
R01-32B-01
R01-32B-01
R01-32B-02
R01-32B-02
R05-51A
R05-51A
R05-51B
R05-51B
R05-53D
R05-53D
R05-61E
R05-63C
R05-63C
R05-63E
R05-63E
R05-65C
R05-65C
S01-01E
S01-01E
S01-02D
S01-02D
S01-02E
S01-02E
S01-03E
S01-03E
S01-04B
S01-04B
S01-04C
S01-04C
S01-04D
S01-04D
S01-04E
S01-04E

[dmelt253]

I just tweaked the code a little and now it works as a public Function!

Public Function areaOwnerSort(ByVal bin1 As String)
Const floorMin As String = "F38-01"
Const floorMax As String = "F41-20"
Const rack1aMin As String = "R01-01A"
Const rack1aMax As String = "R01-16H"
Const rack1bMin As String = "R01-17A"
Const rack1bMax As String = "R01-32H"
Const rack1cMin As String = "R01-33A"
Const rack1cMax As String = "R01-68H"
Const rack23Min As String = "R02-01A"
Const rack23Max As String = "R03-68H"
Const rack45Min As String = "R04-01A"
Const rack45Max As String = "R05-68H"
Const secureMin As String = "S01-01A"
Const secureMax As String = "S02-04E"
Const yardMin As String = "Y01-01"
Const yardMax As String = "Y01-10"
If (bin1 >= floorMin) And (bin1 <= floorMax) Then
    areaOwnerSort = "Matt"
    ElseIf (bin1 >= rack1aMin) And (bin1 <= rack1aMax) Then
    areaOwnerSort = "Jim"
    ElseIf (bin1 >= rack1bMin) And (bin1 <= rack1bMax) Then
    areaOwnerSort = "Dio"
    ElseIf (bin1 >= rack1cMin) And (bin1 <= rack1cMax) Then
    areaOwnerSort = "Matt"
    ElseIf (bin1 >= rack23Min) And (bin1 <= rack23Max) Then
    areaOwnerSort = "Bryan"
    ElseIf (bin1 >= rack45Min) And (bin1 <= rack45Max) Then
    areaOwnerSort = "Tom"
    ElseIf (bin1 >= secureMin) And (bin1 <= secureMax) Then
    areaOwnerSort = "Matt"
    ElseIf (bin1 >= yardMin) And (bin1 <= yardMax) Then
    areaOwnerSort = "Matt"
End If
End Function

[SamT]

Would this work?

Public Function areaOwnerSort(ByVal bin1 As String) As String
    Const Area1 As String = "F38-01"
    Const Area2 As String = "R01-01A"
    Const Area3 As String = "R01-17A"
    Const Area4 As String = "R01-33A"
    Const Area5 As String = "R02-01A"
    Const Area6 As String = "R04-01A"
    Const secure As String = "S01-01A"
    Const yard As String = "Y01-01"
    
    Select Case bin1
      Case Is >= Area1: areaOwnerSort = "Matt"
      Case Is >= Area2: areaOwnerSort = "Jim"
      Case Is >= Area3: areaOwnerSort = "Dio"
      Case Is >= Area4: areaOwnerSort = "Matt"
      Case Is >= Area5: areaOwnerSort = "Bryan"
      Case Is >= Area6: areaOwnerSort = "Tom"
      Case Is >= secure: areaOwnerSort = "Matt"
      Case Is >= yard: areaOwnerSort = "Matt"
    End Select
End Function

[Paul_Hossler]

Rather than hard code all those rules into the macro, you could make a little database on another sheet (which you could hide if you wanted) and just look them up

Probably easier to maintain also

Option Explicit
Public Function AreaOwner(BinName As String) As Variant
    Dim iBins As Long
    
    AreaOwner = CVErr(xlErrRef)
    
    With Worksheets("BinsAndOwners")
        For iBins = 2 To .Rows.Count
            With .Rows(iBins)
                If UCase(.Cells(1).Value) <= UCase(BinName) And UCase(BinName) <= UCase(.Cells(2).Value) Then
                    AreaOwner = .Cells(3).Value
                    Exit Function
                ElseIf Len(.Cells(1).Value) = 0 Then
                    Exit Function
                End If
            End With
        Next iBins
    End With
End Function

[SamT]

Good idea, Paul.

[dmelt253]

Hey Paul,

That was exactly the kind of solution I was hoping for as it would be nice to have a function that I can quickly add new parameters to. As I'm fairly new to VBA I wasn't even sure where to begin though so I will definitely take a good look at your code when I get to the office tomorrow.

Thanks!