Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 dimensional split?
-- Kevin Vaughn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Two dimensional look up | Excel Worksheet Functions | |||
How do I remove split a split window? | New Users to Excel | |||
Transposing from two dimensional to one dimensional | Excel Worksheet Functions | |||
how do i set up a three dimensional table | Excel Discussion (Misc queries) | |||
Create One-Dimensional Array from Two-Dimensional Array | Excel Programming |