2 dimensional split?
 

--
Kevin Vaughn

2 dimensional split?
 

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

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

2 dimensional split?
 

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)


"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

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



"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

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

2 dimensional split?
 

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


"JMB" wrote:

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)


"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

Thanks.
--
Kevin Vaughn


"Vic Eldridge" wrote:

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



"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

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


"Kevin Vaughn" wrote:

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


"JMB" wrote:

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)


"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn

2 dimensional split?
 

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.
--
Kevin Vaughn


"JMB" wrote:

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


"Kevin Vaughn" wrote:

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


"JMB" wrote:

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)


"Kevin Vaughn" wrote:

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.
--
Kevin Vaughn


"Kevin Vaughn" wrote:


--
Kevin Vaughn