Extracting 3 Arrays from 1 Array

[JingleRock]

I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in design
mode only, I am using the following to do exactly that (and it works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

JingleRock

 

[Bob Phillips]

Col_One = Application.Index(vtData, 1)
Col_Two = Application.Index(vtData, 2)
Col_Three = Application.Index(vtData, 3)


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[JingleRock]

Col_One = Application.Index(vtData, 1)
Col_Two = Application.Index(vtData, 2)
Col_Three = Application.Index(vtData, 3)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in design
mode only, I am using the following to do exactly that (and it works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

Bob,

Thanks for your response.
I changed the name of my "child" array to 'Col_Array', since I am
using it for each of the 3 columns.
I got the following to work:

Dim Col_Array As Variant, x As Integer
ReDim Col_Array(UB1 + 1)
For x = 0 To UB1 '= 4
'THIS IS FIRST COLUMN
Col_Array(x) = vtData(0, 0)(x, 0)
Next x

Any comments?

 

[Guest]

Have a look at and try out the following (comments in the code:

Sub blah()
'try this with a clean sheet active:
'set up vtData as you describe
Dim vtData(0 To 4, 0 To 2) As Variant
i = 1
For xx = 0 To 4
For yy = 0 To 2
vtData(xx, yy) = i
i = i + 1
Next: Next

UB1 = 4 ' you said 4 was UBound of dim1
Range("A1:C5") = vtData 'just to visualise although I tried your line too:
ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0) 'and got
all 15 cells the same
Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0) 'this loop went through without any error
Next x
'however you may find this bit useful:
Col1 = Application.Transpose(Application.Index(vtData, 0, 1))
Col2 = Application.Transpose(Application.Index(vtData, 0, 2))
Col3 = Application.Transpose(Application.Index(vtData, 0, 3))
'Col1 above is VERY similar to your Col_One

'but to get things into strings you may not need to use child arrays:
For y = 0 To 2
For x = 0 To 4
Select Case y
Case 0
ArCol1 = ArCol1 & vtData(x, y) & ","
Case 1
Arcol2 = Arcol2 & vtData(x, y) & ","
Case 2
Arcol3 = Arcol3 & vtData(x, y) & ","
End Select
Next: Next
'now ArCol1 contains a string similar to what I think you want
Stop 'comment out after examiningthe Locals window
End Sub

 

[Dana DeLouis]

I want to create 3 child arrays (one for each column)

Do I need to use WorksheetFunction.Transpose?

Hi. In "general", one way is to use Transpose.
Any ideas here that can help?

Sub Demo()
Dim m As Variant 'Matrix
Dim v As Variant 'Vector
Dim s As String 'String
Dim c As Long 'Column
Const All As Long = 0 'All Rows or Columns

m = [A1:C5].Value
With WorksheetFunction
For c = 1 To 3
v = .Index(m, All, c)
v = .Transpose(v)
s = Join(v, ",")
Debug.Print s
Next c
End With
End Sub

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),

I like to use "Resize," as I find it easier to read / understand.

Sub Demo2()
Dim m As Variant 'Matrix
m = [A1:C5].Value

[A10].Resize(5, 3) = m
End Sub


--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007

I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in design
mode only, I am using the following to do exactly that (and it works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

JingleRock

 

[JingleRock]

I want to create 3 child arrays (one for each column)
Do I need to use WorksheetFunction.Transpose?

Hi. In "general", one way is to use Transpose.
Any ideas here that can help?

Sub Demo()
Dim m As Variant 'Matrix
Dim v As Variant 'Vector
Dim s As String 'String
Dim c As Long 'Column
Const All As Long = 0 'All Rows or Columns

m = [A1:C5].Value
With WorksheetFunction
For c = 1 To 3
v = .Index(m, All, c)
v = .Transpose(v)
s = Join(v, ",")
Debug.Print s
Next c
End With
End Sub

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),

I like to use "Resize," as I find it easier to read / understand.

Sub Demo2()
Dim m As Variant 'Matrix
m = [A1:C5].Value

[A10].Resize(5, 3) = m
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007

I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in design
mode only, I am using the following to do exactly that (and it works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

Thanks Bob, again, p45cal, and Dana. I learned a lot from your posts.

Re: my application -- I am hereby providing add'l info. I was using
static arrays at first, but I now (with the benefit of what I have
learned) must use dynamic ones (actually, the number of columns is a
constant) as my application requires.

I posted (most of) the code below in Msg #3; I am flabbergasted that
this works. Can anyone explain why?

Dim vtData As Variant
Dim Col_Array As Variant, x As Integer, UB1 As Integer
vtData = Bloomberg download code (imports variable # of rows, 3
columns)
're: above data -- Col 1 is all dates; Col 2 is all prices;
Col 3 is all $ amounts
UB1 = UBound(vtData(0, 0), 1)
ReDim Col_Array(UB1 + 1)
For x = 0 To UB1
'THIS IS FIRST COLUMN (with Dimension2 Index = 0)
Col_Array(x) = vtData(0, 0)(x, 0)
Next x

I am using the same ReDim stmt and the same Col_Array(x) name for Col
2 and for Col 3.

JingleRock

 

[Guest]

I get errors with the likes of
UBound(vtData(0, 0), 1)
and
vtData(0, 0)(x, 0)

Let's have the code that creates vtData (or vtData(0,0) from Bloomberg.

--
p45cal

I want to create 3 child arrays (one for each column)
Do I need to use WorksheetFunction.Transpose?

Hi. In "general", one way is to use Transpose.
Any ideas here that can help?

Sub Demo()
Dim m As Variant 'Matrix
Dim v As Variant 'Vector
Dim s As String 'String
Dim c As Long 'Column
Const All As Long = 0 'All Rows or Columns

m = [A1:C5].Value
With WorksheetFunction
For c = 1 To 3
v = .Index(m, All, c)
v = .Transpose(v)
s = Join(v, ",")
Debug.Print s
Next c
End With
End Sub

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),

I like to use "Resize," as I find it easier to read / understand.

Sub Demo2()
Dim m As Variant 'Matrix
m = [A1:C5].Value

[A10].Resize(5, 3) = m
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007

I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in design
mode only, I am using the following to do exactly that (and it works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

Thanks Bob, again, p45cal, and Dana. I learned a lot from your posts.

Re: my application -- I am hereby providing add'l info. I was using
static arrays at first, but I now (with the benefit of what I have
learned) must use dynamic ones (actually, the number of columns is a
constant) as my application requires.

I posted (most of) the code below in Msg #3; I am flabbergasted that
this works. Can anyone explain why?

Dim vtData As Variant
Dim Col_Array As Variant, x As Integer, UB1 As Integer
vtData = Bloomberg download code (imports variable # of rows, 3
columns)
're: above data -- Col 1 is all dates; Col 2 is all prices;
Col 3 is all $ amounts
UB1 = UBound(vtData(0, 0), 1)
ReDim Col_Array(UB1 + 1)
For x = 0 To UB1
'THIS IS FIRST COLUMN (with Dimension2 Index = 0)
Col_Array(x) = vtData(0, 0)(x, 0)
Next x

I am using the same ReDim stmt and the same Col_Array(x) name for Col
2 and for Col 3.

JingleRock

 

[Bob Phillips]

That is an array of arrays, something like

myArray = Array(Array("A", "B", "C"), _
Array("D", "E", "F"), _
Array("G", "H", "I"))

although these are single dimension arrays

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

I get errors with the likes of
UBound(vtData(0, 0), 1)
and
vtData(0, 0)(x, 0)

Let's have the code that creates vtData (or vtData(0,0) from Bloomberg.

--
p45cal

I want to create 3 child arrays (one for each column)
Do I need to use WorksheetFunction.Transpose?

Hi. In "general", one way is to use Transpose.
Any ideas here that can help?

Sub Demo()
Dim m As Variant 'Matrix
Dim v As Variant 'Vector
Dim s As String 'String
Dim c As Long 'Column
Const All As Long = 0 'All Rows or Columns

m = [A1:C5].Value
With WorksheetFunction
For c = 1 To 3
v = .Index(m, All, c)
v = .Transpose(v)
s = Join(v, ",")
Debug.Print s
Next c
End With
End Sub

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),

I like to use "Resize," as I find it easier to read / understand.

Sub Demo2()
Dim m As Variant 'Matrix
m = [A1:C5].Value

[A10].Resize(5, 3) = m
End Sub

--
HTH :>)
Dana DeLouis
Windows XP & Excel 2007





I have a 2-dimensional array [vtData(0, 0)] that is Dimensioned (Dim
vtData As Variant); its Lbound for each dimension is 0 and its UBound
for dimension1 is 4 and its UBound for dimension2 is 2; therefore, 15
elements. (This array is imported into my VBA Code from Bloomberg.)

I do NOT want to paste this array into a worksheet; however, in
design
mode only, I am using the following to do exactly that (and it
works):

ActiveSheet.Range(Cells(8, 1), Cells(8 + UB1, 3)) = vtData(0, 0),
where UB1 is UBound for dimension1.

OK, I want to create 3 child arrays (one for each column) from the
parent array. (My objective is to connect the individual elements for
each column into one string, using a delimiter, and then paste that
string into a specified cell in my worksheet.) This is my non-working
code:

Dim Col_One As Variant, x As Integer
ReDim Col_One(UB1 + 1)
For x = 0 To UB1
Col_One(x) = vtData(x, 0)
Next x
When stepping-thru my code:
when x = 0, there are no errors;
when x = 1, I get the dreaded 'subscript out of range' error.

Do I need to use WorksheetFunction.Transpose?

Thanks Bob, again, p45cal, and Dana. I learned a lot from your posts.

Re: my application -- I am hereby providing add'l info. I was using
static arrays at first, but I now (with the benefit of what I have
learned) must use dynamic ones (actually, the number of columns is a
constant) as my application requires.

I posted (most of) the code below in Msg #3; I am flabbergasted that
this works. Can anyone explain why?

Dim vtData As Variant
Dim Col_Array As Variant, x As Integer, UB1 As Integer
vtData = Bloomberg download code (imports variable # of rows, 3
columns)
're: above data -- Col 1 is all dates; Col 2 is all prices;
Col 3 is all $ amounts
UB1 = UBound(vtData(0, 0), 1)
ReDim Col_Array(UB1 + 1)
For x = 0 To UB1
'THIS IS FIRST COLUMN (with Dimension2 Index = 0)
Col_Array(x) = vtData(0, 0)(x, 0)
Next x

I am using the same ReDim stmt and the same Col_Array(x) name for Col
2 and for Col 3.

JingleRock