2 dimensional split?

[Kevin Vaughn]

--
Kevin Vaughn

[Kevin Vaughn]

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

[JMB]

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

[Vic Eldridge]

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

[Kevin Vaughn]

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

[Kevin Vaughn]

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

[JMB]

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

[Kevin Vaughn]

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