Avoiding "On Error Resume Next" if possible

[TheWennerWoman]

I am using a function to validate email addresses on a sheet. Most of the time there is just the one email address in a cell but there are occasions when there are two separated by a semi-colon

donald.duck@disneyworld.com; mickey.mouse@disneyworld.com

So before passing the email addresses to the function, I am splitting like this

For a = 2 To lrow2
'On Error Resume Next
emailtoparse = Sheet6.Range("B" & a).Value
result2 = Split(emailtoparse, ";")
firstemail = result2(0)
secondemail = LTrim(result2(1))

However, I am getting a "subscript out of range" error on the variable secondemail whenever there's just a single email address (I assume because result2(1) doesn't exist in those cases).

I can fudge my way through with an On Error Resume Next but I don't like doing that because it'a apparently bad practice

Is there another way I can try?

Thank you for reading.

 

[Alex Blakenburg]

Does this give you any ideas ?

Sub testSplit()

    Dim emailtoparse As String
    Dim result2 As Variant
    Dim j As Long
    
    emailtoparse = "donald.duck@disneyworld.com"
    result2 = Split(emailtoparse, ";")
    
    For j = 0 To UBound(result2)
        ' do what you need to do with each address
        Debug.Print Trim(result2(j))
    Next j
    
End Sub

 

[TheWennerWoman]

Perfect thanks

 

[Alex Blakenburg]

You're welcome.

 

[FormR]

Hi, just for demonstration purposes, here's another way you could approach it.

Sub testSplit()

    Dim emailtoparse As String
    Dim result2 As Variant
    
    emailtoparse = "donald.duck@disneyworld.com"
    
    For Each result2 In Split(emailtoparse, ";")
        ' do what you need to do with each address
        Debug.Print Trim(result2)
    Next result2
    
End Sub