Consulting

Results 1 to 6 of 6

Thread: VBA String Range Detection

  1. #1

    VBA String Range Detection

    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

  2. #2
    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

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,948
    Location
    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    Good idea, Paul.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    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!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •