Subscript out of range error

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • almaroc
    New Member
    • Nov 2011
    • 48

    Subscript out of range error

    I am using a function to delete the "@" from address field. this function is also supposed to remove the unnecessary "0" from the beginning of a street address. i am this function in a simple query but this error "Subscript out of range"
    Code:
    1.Public Function StripDup(ByVal varWith As Variant) As String
    2.      Dim intX As Integer, intY, intZ As Integer
    3.      Dim strWith As String
    
    4.     If IsNull(varWith) Then
    5.         StripDup = ""
    6.       Exit Function
    7.     End If
          
    8.     StripDup = Trim(varWith)
    9.     varWith = Split(StripDup, " ")
    10.     For intX = 0 To UBound(varWith) Step 1
    11.        If Left(varWith(intX), 1) = "@" Then
    12.            varWith(intX) = LTrim(varWith(intX))
    13.        End If
    14.     Next intX
         
    15.    For intY = 0 To Len(varWith(0)) Step 1
    16.        If Left(varWith(0), 1) <> "0" Then Exit For 17.Else varWith(0) = LTrim(varWith(0))
    18.    Next intY
    19.    StripDup = ""
    19.    For intZ = 0 To UBound(varWith)
    20.    StripDup = StripDup & " " & varWith(intZ)
    21.    Next intZ
    22.    StripDup = LTrim(StripDup)
        
    23. End Function
    Attached Files
  • Rabbit
    Recognized Expert MVP
    • Jan 2007
    • 12517

    #2
    What line is it erroring out on? I see multiple places where it can go out of range.

    Comment

    • almaroc
      New Member
      • Nov 2011
      • 48

      #3
      I've fixed the out of range error how i am not getting the result that all i am getting is the value in table. my goal is to remove the @ from the address and also delete the zeros "0" from the beginning of the address.

      Comment

      • Rabbit
        Recognized Expert MVP
        • Jan 2007
        • 12517

        #4
        That's a different question. Please post how you solved the out of range error and then create a new thread for your new question.

        Comment

        • almaroc
          New Member
          • Nov 2011
          • 48

          #5
          I have the wrong variable in line 20. I just replaced IntxX with Intz

          Comment

          • almaroc
            New Member
            • Nov 2011
            • 48

            #6
            i have posted the question under How to delete unwanted characters from an address field.

            Comment

            Working...