VBA String Range Detection

dmelt253

New Member
Joined
Jan 12, 2016
Messages
8
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:
MinMaxArea Owner
F38-01F41-20Matt
R01-01AR01-16HJim
R01-17AR01-32HDio
R01-33AR01-68HMatt
R02-01AR03-68HBryan
R04-01AR05-68HTom
S01-01AS02-04EMatt
Y01-01Y01-10Matt


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!

Code:
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

Note: A user on another forum suggested I just use Vlookup but unfortunately this won't work as this function is going to be part of a much bigger Macro and I want it to be somewhat "smart". Plus there are thousands of locations in this warehouse so the list would be huge and always changing.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Leith Ross

Well-known Member
Joined
Mar 17, 2008
Messages
1,874
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
Hello dmelt253,

What would the number sequences be for R01-01A to R01-16H?
R01-01A, R01-01B, R01-01C ... R01-01H
R01-02A, R01-02B, R01-02C ... R01-02H, etc.
 
Last edited:

dmelt253

New Member
Joined
Jan 12, 2016
Messages
8
Hello dmelt253,

What would the number sequences be for R01-01A to R01-16H?
R01-01A, R01-01B, R01-01C ... R01-01H
R01-02A, R01-02B, R01-02C ... R01-02H, etc.

Ross that is correct.

R01 = Rack 1
___-01_ = Is like the column so it changes as you move left or right. As you move to the right the numbers go up 01, 02, 03, etc.
A, B, C, etc. is the shelf or A starting on the bottom and H at the very top. There are different configurations for shelves so they won't always go up to H.
And finally if there is another -01, -02, -03, etc. it means the bin has been subdivided into smaller locations.
 

dmelt253

New Member
Joined
Jan 12, 2016
Messages
8
I just tweaked the code a little and now it works as a public Function!
Code:
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
 

Forum statistics

Threads
1,136,779
Messages
5,677,684
Members
419,712
Latest member
LearningCR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top