PDA

View Full Version : [SOLVED] SPLIT STRING issue with array if element not found



Al@n
01-12-2017, 03:48 AM
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:

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

I would be gratefull for any advice/assistance in resolving this issue.
Regards

mana
01-12-2017, 05:13 AM
MsgBox UBound(myarray)

Al@n
01-12-2017, 05:38 AM
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
01-12-2017, 07:09 AM
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
01-12-2017, 09:42 AM
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
01-12-2017, 05:09 PM
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
01-12-2017, 08:48 PM
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
01-13-2017, 03:39 AM
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
01-13-2017, 04:14 AM
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
01-13-2017, 05:22 AM
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:

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


Thanks to everyone for the assistance given.

mikerickson
01-13-2017, 05:28 PM
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