• Home
 · 
• String Manipulation
 · 
• This function will allow you to determine if a value is with in the range of another. Example is 12

This function will allow you to determine if a value is with in the range of another. Example is 12

This function will allow you to determine if a value is with in the range of another. Example is 123 inthe range of 100-200?

Rate This function will allow you to determine if a value is with in the range of another. Example is 12

• 1
• 2
• 3
• 4
• 5

(2(2 Vote))

  Dim Found As Boolean
  Dim I As Long
  Dim MinVal As Variant, MaxVal As Variant
  Dim tRange As String
  Range = Trim(Range)
  If Len(Range) = 0 Then
    IsInRange = False
    Exit Function
  End If
  Range = Replace(Range, " ", "") ' remove any spaces
  Range = Replace(Range, "to", "-") ' replace any 'to's to '-'
  Range = Replace(Range, "--", "-*")
  Found = False
  For I = 1 To ItemCount(Range, ",")
      ' first Char '-' ?
      tRange = Item(Range, ",", I)
      If Left(tRange, 1) = "-" Then
        ' Single Value or Range
        If InStr(Mid(tRange, 2), "-") Then
          ' Range
          MinVal = -Val(Item(Mid(tRange, 2), "-", 1))
          MaxVal = IIf(Left(Item(Mid(tRange, 2), "-", 2), 1) = "*", _
                      -Val(Item(tRange, "*", 2)), _
                      Val(Item(Mid(tRange, 2), "-", 2)))
          If Between(v, MinVal, MaxVal) Then
            IsInRange = True
            Exit Function
          End If
        Else
          If (v = Val(tRange)) Then
            IsInRange = True
            Exit Function
          End If
        End If
      Else
        ' Single Value or Range
        If InStr(Mid(tRange, 2), "-") Then
          ' Range
          MinVal = Val(Item(Mid(tRange, 1), "-", 1))
          MaxVal = IIf(Left(Item(Mid(tRange, 2), "-", 2), 1) = "*", _
                      -Val(Item(tRange, "*", 2)), _
                      Val(Item(Mid(tRange, 2), "-", 2)))
          If Between(Val(v), MinVal, MaxVal) Then
            IsInRange = True
            Exit Function
          End If
        Else
          If (v = Val(tRange)) Then
            IsInRange = True
            Exit Function
          End If
        End If
      End If
  Next I
End Function

Public Function Between(V1 As Variant, Min As Variant, Max As Variant) As Boolean
  If Min > Max Then
    Dim V3
    V3 = Min
    Min = Max
    Max = V3
  End If
  Between = ((V1 >= Min) And (V1 <= Max))
End Function

Public Function ItemCount(ByVal A As String, _
                          Optional ByRef S As String = " ") As Integer
    
    ' This function will count the number of items with in a string ('A') separated by 'S'
  A = Trim(A)
  If A = "" Then ItemCount = 0: Exit Function
' Revised:  Fri Jul-13-2001 @ 12:07 PM  By: jfouch
  ItemCount = UBound(Split(A, S)) + 1
End Function

Public Function Item(ByVal T As String, _
                     ByVal S As String, _
                     ByVal N As Long, _
                     Optional ByVal Count As Long = -1) As String
  
  ' This function will return item # ('N') of string('T') separated by ('S')
  ' Revised:  Sat Jan-13-2001 @ 10:17 AM  By: jfouch
  ' per recomemditaion by Arron Wampler to send the count if it's known to speed
  ' up processing
  Dim NOI As Long
  If T = "" Then Exit Function
  If Count < 1 Then
    NOI = UBound(Split(T, S)) + 1
  Else
    NOI = Count
  End If
  ' Revised:  Fri Jul-13-2001 @ 12:11 PM  By: jfouch
  If N > NOI Then
    Item = ""
    Exit Function
  Else
    Item = Split(T, S)(N - 1)
    Exit Function
  End If
End Function