SPLIT STRING issue with array if element not found

[Al@n]

Hi.
I'm trying to take data from a cell in which there is usualy 3 lines of text. The string is split based on vbLf, with the last array being split again based on "()". This all works fine aslong as there are 3 elements in the initial string. However, should an element be missing, the macro throws up a type mismatch error. Please see code below:
[VBA]
If ActiveCell.Value <> 0 Then
myarray = Split(ActiveCell.Value, vbLf) 'breaks up cell based on linebreaks
PWork1$ = myarray(0)
PWork2$ = myarray(1)
PWork3$ = myarray(2)
Origin$ = Split(Split(myarray(2), "(")(1), ")")(0) 'returns originator name

Else: PWork1$ = ""
PWork2$ = ""
PWork3$ = ""
Origin$ = ""
End If
[/VBA]
I would be gratefull for any advice/assistance in resolving this issue.
Regards

[mana]

MsgBox UBound(myarray)

[Al@n]

MsgBox UBound(myarray)

Thank you Mana. This always returns the value 2, I suspect because of the way the original string is populated with vbLf being triggered even if there is nothing in the source text box. This is something that can be addressed in the future when the original cell is populated but will not overcome my issue as it stands.

[Bob Phillips]

Not sure I fully understand, but does this do it for you?

Dim myarray As Variant
Dim PWork1 As String, PWork2 As String, PWork3 As String, Origin As String

    With ActiveCell
    
        If .Value <> 0 Then
        
            myarray = Split(Replace(.Value, vbLf & vbLf, vbLf), vbLf) 'breaks up cell based on linebreaks
            PWork1 = myarray(0)
            PWork2 = IIf(UBound(myarray) > 0, myarray(1), "")
            PWork3 = IIf(UBound(myarray) > 1, myarray(2), "")
            Origin = Split(Split(myarray(UBound(myarray)), "(")(1), ")")(0) 'returns originator name
        Else
        
            PWork1 = ""
            PWork2 = ""
            PWork3 = ""
            Origin = ""
        End If
    End With

[Al@n]

Thank you xld. I've tried your suggestion but it is now giving "Run time error '9'. Subscript out of range." I don't fully understand the code you provided but will try to explain my requirement a bit better than I did originally.
In a cell on the worksheet there are 3 items formatted with a vbLf between them. e.g.

Location - sourced from text box on a user form...................This field isn't mandatory at the moment
WorkType - sourced from text box on a user form....................This field isn't mandatory at the moment
(Originator)(hours) -sourced from username and a dropdown box.......... This IS mandatory

What I'm trying to do is split each of the items and put them in their own cells in another work book. i.e. Location, WorkType, Originator.

The original code works as long as data is entered in each of the fields but fails if one of the first two elements, Location/WorkType has no data in them.
I've been trying to resolve the issue in my code by allowing myarray(0) and or myarray(1) to accept a nil entry or if it is a nil entry, replace it with "N/A," but because of my limited VBA experience, particularly with strings and the split function, am tearing my hair out.

Any advice would be greatly appreciated.

[Leith Ross]

Hello Al@n,

You might find this macro useful. It assumes there will be only one set of parentheses in the text. Blank cells are ignored.

It first validates that the cell's text contains both a left and right parenthesis. If that is not found then nothing happens. Once validated, the origin is extracted to a variable for use later.

The array is derived from splitting the cell using the vbLf character. Each element of the array is checked if it contains a left parenthesis. If so, the origin will be stripped from the element's text and the new text assigned to the element. The total number of parsed items will be equal to the upper bound of the array plus one.

Sub ParseTest()


    Dim Cell    As Range
    Dim Data    As Variant
    Dim j       As Long
    Dim k       As Long
    Dim n       As Long
    Dim Origin  As String


    
        Set Cell = ActiveCell
        
            j = InStr(1, Cell, "(")
            k = InStr(j + 1, Cell, ")")
            
            If j <> 0 And k <> 0 Then
                Origin = Mid(Cell, j + 1, k - j - 1)
                Data = Split(Cell, vbLf)
                For n = 0 To UBound(Data)
                    j = InStr(1, Data(n), "(")
                    If j <> 0 Then
                        Data(n) = Left(Data(n), j - 1)
                    End If
                Next n
            End If


End Sub

[SamT]

I Use Excel 2002, so this is probably the wrong syntax, but I hope you get the idea

        PWork3 = IIf(UBound(myarray) > 1, myarray(2), "") 
        Origin = IIf(UBound(myarray) =3, Split(myarray(3), "(")
If IsArray(Origin) then    ' "(" is present

[Al@n]

Thanks Leith.
The provided macro sorts out the 3rd element of the string with the originators name in parenthesis, however this is not the issue I have. The first two elements of the string may or may not have anything in them and I think that this is where my attempt a coding falls down. My code works fine as long as all 3 elements contain text.
Thanks again

[Aussiebear]

My code works fine as long as all 3 elements contain text.

To my way of thinking, simply test to see if all three items are available, if so then proceed with your code. If not then prompt for the information.

[Al@n]

Thank you all. By taking/learning alittle something from your replies I have managed to get the macro to run as I require. It may not be the most efficient coding however, it works. This is how I resolved the issue:
[VBA]
If ActiveCell.Value <> 0 Then ' sorting out cell data

myarray = Split(ActiveCell.Value, vbLf) 'breaks up cell based on linebreaks

If UBound(myarray) = 1 Then 'if only 2 arrays
PWork1= myarray(0)
PWork2 = myarray(1)
Origin = Split(Split(myarray(1), "(")(1), ")")(0)

'----Transfer results so the daa goes in correct columns
PWork3 = PWork2
PWork2 = PWork1
PWork1 = "N/A"
'-------------------------------------------------------
End If
If UBound(myarray) = 2 Then 'if 3 arrays
PWork1 = myarray(0)
PWork2 = myarray(1)
PWork3 = myarray(2)
Origin = Split(Split(myarray(2), "(")(1), ")")(0)
End If
Else

PWork1 = ""
PWork2 = ""
PWork3 = ""
Origin = ""
End If
[/VBA]

Thanks to everyone for the assistance given.

[mikerickson]

Try

If ActiveCell.Value <> 0 Then 
    myarray = Split(ActiveCell.Value & vbLf & vbLf & vbLF, vbLf) 'breaks up cell based on linebreaks
    PWork1$ = myarray(0) 
    PWork2$ = myarray(1) 
    PWork3$ = myarray(2) 
    Origin$ = Split(Split(myarray(2) & "()" , "(")(1), ")")(0) 'returns originator name
     
Else: PWork1$ = "" 
    PWork2$ = "" 
    PWork3$ = "" 
    Origin$ = "" 
End If