2 dimensional split?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kevin Vaughn - 23 Mar 2006 18:26 GMT

Signature

Kevin Vaughn

Reply to this Message

Kevin Vaughn - 23 Mar 2006 18:40 GMT

Oops. Sorry. It seems way too easy to create an empty post (I accidentally
hit Enter,) but I take full responsibility. Anyway, on to my question:

I have a cell, for instance, with the following data (each line seperated by
chr(10):

3/1/2006 6:00:00 PM;P
3/2/2006 5:00:00 PM;P
3/11/2006 10:00:00 AM;P
3/22/2006 7:00:00 PM;P
3/1/2006 7:00:00 PM;B
3/2/2006 6:00:00 PM;B
3/11/2006 11:00:00 AM;B
3/22/2006 6:00:00 PM;B

From the Immediate window, I tried the following:

vaArray = split(activecell.Value, chr(10))
? vaarray(0)
3/1/2006 6:00:00 PM;P
vaarray2 = split(vaarray(0), ";")
? vaarray2(1)
P

The following line gives me the error: Run Time Error '13': Type Mismatch
vaarray = split(split(activecell.Value, chr(10)), ";")

I know I can get the information differently, but I was kind of hoping to be
able to create a 2 dimensional array in one shot, and then, for instance,
count the number of "P", "B" or there could be 3 other letters that aren't
shown here (C, I, or U for the curious.)

Is what I'm trying possible but I am using the incorrect syntax, or is it
impossible? I am guessing the latter, but thought I would ask here to make
sure. Thanks.

Signature

Kevin Vaughn

Reply to this Message

Randy Harmelink - 23 Mar 2006 21:13 GMT

Since the SPLIT() function requires a string expression to operate
upon, I would agree with your regret that SPLIT(SPLIT()) is impossible.

Reply to this Message

JMB - 24 Mar 2006 09:02 GMT

One way is to replace Chr(10) with a semicolon then split the string using ;
as the delimiter.

VarArray = Split(Replace(ActiveCell.Value, _
Chr(10), ";", 1, -1, vbTextCompare), _
";", -1, vbTextCompare)

> Oops. Sorry. It seems way too easy to create an empty post (I accidentally
> hit Enter,) but I take full responsibility. Anyway, on to my question:
[quoted text clipped - 31 lines]
> impossible? I am guessing the latter, but thought I would ask here to make
> sure. Thanks.

Reply to this Message

Kevin Vaughn - 24 Mar 2006 17:28 GMT

Ok, so that makes every other array item the one I would count. This is
useful. Thanks.

Signature

Kevin Vaughn

> One way is to replace Chr(10) with a semicolon then split the string using ;
> as the delimiter.
[quoted text clipped - 38 lines]
> > impossible? I am guessing the latter, but thought I would ask here to make
> > sure. Thanks.

Reply to this Message

JMB - 26 Mar 2006 23:45 GMT

Or loop through the entire array and count the elements that equal P, B, C,
I, U. I'm assuming you want a separate count for each item.

Sub test()
Dim i As Long
Dim Pcount As Long
Dim Bcount As Long
Dim Ccount As Long
Dim Icount As Long
Dim Ucount As Long
Dim VarArray As Variant

VarArray = Split(Replace(ActiveCell.Value, _
Chr(10), ";", 1, -1, vbTextCompare), ";", _
-1, vbTextCompare)

For i = LBound(VarArray) To UBound(VarArray)
Select Case UCase(VarArray(i))
Case "P": Pcount = Pcount + 1
Case "B": Bcount = Bcount + 1
Case "C": Ccount = Ccount + 1
Case "I": Icount = Icount + 1
Case "U": Ucount = Ucount + 1
End Select
Next i

MsgBox Pcount & " " & Bcount & " " & _
Ccount & " " & Icount & " " & Ucount

End Sub

> Ok, so that makes every other array item the one I would count. This is
> useful. Thanks.
[quoted text clipped - 41 lines]
> > > impossible? I am guessing the latter, but thought I would ask here to make
> > > sure. Thanks.

Reply to this Message

Kevin Vaughn - 27 Mar 2006 17:51 GMT

That's true too. Although, if I wanted to test for the possibility of an
erroneous character in that spot, I would still need to test every other
element. Thanks for the idea.

Signature

Kevin Vaughn

> Or loop through the entire array and count the elements that equal P, B, C,
> I, U. I'm assuming you want a separate count for each item.
[quoted text clipped - 72 lines]
> > > > impossible? I am guessing the latter, but thought I would ask here to make
> > > > sure. Thanks.

Reply to this Message

Vic Eldridge - 24 Mar 2006 09:27 GMT

Hi Kevin,

> I was kind of hoping to be able to create a 2 dimensional array in one shot...

Split is only capable of producing a 1 dimensional array.

Regards,
Vic Eldridge

Reply to this Message

Kevin Vaughn - 24 Mar 2006 17:28 GMT

Thanks.

Signature

Kevin Vaughn

> Hi Kevin,
>
[quoted text clipped - 40 lines]
> > impossible? I am guessing the latter, but thought I would ask here to make
> > sure. Thanks.

Reply to this Message

Randy Harmelink - 24 Mar 2006 13:57 GMT

How about using a function to simulate the 2-dimensional array? For
example:

Dim vData As Variant
Sub Test()
vData = Split(Replace(Range("H18"), Chr(10), ";"), ";")
Debug.Print fData(7, 1)
Debug.Print fData(7, 2)
End Sub
Function fData(iRow, iCol)
fData = vData(2 * iRow + iCol - 3)
End Function

Reply to this Message