none
On error fails to work second time through

    Question

  • This is a problem in Excel 2007 running under windows 7.

    The following code is designed to split a series of comma delimited text strings located in cells b3 and below.

    eg "a, b, c" and

    "d, e, f, g" etc

    It works first time through for the first text string but the second time through with the second, the last substring causes an error

    "Unable to get the find property of the worksheetFunction class"

    This is the error message that would be generated first time if there was no "On error" statement. It seems as if the statement "On error goto 3" only functions once.

    many thanks for looking at this.

    Sub splitwords()
    i = 3
    1
    mystring = Range("b" & i)
    If mystring = Empty Then GoTo 4
    2
    lastrows = Cells(Rows.Count, 1).End(xlUp).Row + 1
    On Error GoTo 3
    A = Left(mystring, WorksheetFunction.Find(",", mystring, 1) - 1) 'produces an error if no commas left in mystring
    mystring = Mid(mystring, WorksheetFunction.Find(",", mystring, 1) + 1, Len(mystring)) 'removes previous string
    If Left(mystring, 1) = " " Then mystring = Mid(mystring, 2)

    Cells(lastrows, 1).Value = A
    GoTo 2
    3
    On Error GoTo 0

    Cells(lastrows, 1).Value = mystring
    i = i + 1
    GoTo 1
    4
    End Sub

    Monday, January 31, 2011 9:31 AM

Answers

  • Hi Stewart,

    There is a Split method that will let you split a string based on a delimiter.

    Here is a short ond unrealistic example

    Sub splitwords()
        myString = "a, b"
        myValues = Split(myString, ",")
            
        For Each Value In myValues
            Debug.Print Trim(Value)
        Next
        
    End Sub

     

    Means you don't need to worry about whether there is a , or not in the string.

    I know Error Handling in VBA is done using the GoTo statement but try not to use GoTo on it's own. It makes code really difficult to follow because the current line of executing code jumps up and down and all around. 

    Hopefullf the Split method will help.

     


    …we each have more potential than we might ever presume to guess. (Blog: http://dsmyth.blogspot.com/)
    • Marked as answer by stewpipes Monday, January 31, 2011 6:05 PM
    • Unmarked as answer by stewpipes Monday, January 31, 2011 6:07 PM
    • Marked as answer by stewpipes Monday, January 31, 2011 6:08 PM
    Monday, January 31, 2011 1:52 PM

All replies

  • Hello Stewpipes,

    Try move the find result into a variable and then test the variable value prior to the left function. It seems to be raising an error because the left function is receiving an invalid second parameter (-1).

    E.g.:

    myIndex = WorksheetFunction.Find(",", mystring, 1)

    if myIndex > 0 then

    A = Left(mystring, myIndex - 1)

    end if

    Hope it helps!


    Tiago Cardoso VB / VBA Analyst
    Monday, January 31, 2011 11:08 AM
  • Hi Tiago

    Many thanks for the suggestions.  I tried these but the same error persists.  The code below tries to parse the same string twice. It is the find that generates the error. It seems really odd to me that if you try to process the same comma delimited string twice the On error GOTO" statement works the first time through and correctly send the macro to label 3 but with the second identical string, it produces an error message rather than sending the programme to label 3.

    Sub splitwords()
    1
    mystring = "a, b"
    2
    lastrows = Cells(Rows.Count, 1).End(xlUp).Row + 1
    On Error GoTo 3
    commaposn = WorksheetFunction.Find(",", mystring)
    A = Left(mystring, commaposn) 'produces an error if no commas left in mystring
    mystring = Mid(mystring, WorksheetFunction.Find(",", mystring, 1) + 1, Len(mystring)) 'removes previous string
    If Left(mystring, 1) = " " Then mystring = Mid(mystring, 2)

    Cells(lastrows, 1).Value = A
    GoTo 2
    3
    On Error GoTo 0

    Cells(lastrows, 1).Value = mystring
    i = i + 1
    GoTo 1
    4
    End Sub


    Stewart Murray
    Monday, January 31, 2011 12:59 PM
  • Hi Stewart,

    There is a Split method that will let you split a string based on a delimiter.

    Here is a short ond unrealistic example

    Sub splitwords()
        myString = "a, b"
        myValues = Split(myString, ",")
            
        For Each Value In myValues
            Debug.Print Trim(Value)
        Next
        
    End Sub

     

    Means you don't need to worry about whether there is a , or not in the string.

    I know Error Handling in VBA is done using the GoTo statement but try not to use GoTo on it's own. It makes code really difficult to follow because the current line of executing code jumps up and down and all around. 

    Hopefullf the Split method will help.

     


    …we each have more potential than we might ever presume to guess. (Blog: http://dsmyth.blogspot.com/)
    • Marked as answer by stewpipes Monday, January 31, 2011 6:05 PM
    • Unmarked as answer by stewpipes Monday, January 31, 2011 6:07 PM
    • Marked as answer by stewpipes Monday, January 31, 2011 6:08 PM
    Monday, January 31, 2011 1:52 PM
  • Hi Derek

    Many thanks.  Most helpful and it works perfectly even if I am still bemused as to why the on error approach fails the second time through with apparently identical data.

    Stewart 


    Stewart Murray
    Monday, January 31, 2011 11:08 PM
  • "On Error"  sets an Error Trap
    When you have an error, the Error Trap becomes active
    You cannot trap errors inside an active Error Trap
    You must 'deactivate' the Error Trap
    You do this with a "Resume" statement
     
    Quick and dirty ...
     
    ...
    GoTo 2
    3
    Resume 33
    33
    On Error GoTo 0
    ...
     
    I'll leave you to design a more elegant (and robust) approach
     

    Enjoy,
    Tony
    www.WordArticles.com
    Tuesday, February 01, 2011 11:42 AM