Array function, two dimensions?? and worksheet arrays
 

Hi

example A
Dim A As Variant
A = Array(10,20,30)
B = A(2)
the above is straight from Excel help on array functions.

If have used two dimension arrays like this befo
example B
dim stuff(20,20) as integer

where stuff( var1, var2) gets me to where I want.

Q1) Can you 'do' exampleA in two dimensions?

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Thanks much,

--
Neal Z

Array function, two dimensions?? and worksheet arrays
 

Here is an example from "real life". I did not take anything out of this to make it easier to understand, so you may need to study
it for some time. This is a 2-dimension array example and it will show you how to load up both dimensions with variables from the
workbook.

Sub GetTeamExtract()
Dim MovingExtract(100, 16) As Variant
Dim E As Long
Dim Z As Long

Sheets("Team Data Table").Activate
Range("B7").Activate
E = 0
Do Until ActiveCell = "" 'loop through the list of Teams and copy their data
'to their data table

ActiveWorkbook.Names.Add Name:="Bookmark", RefersTo:=ActiveCell
N = ActiveCell.Text 'set variable to = Team names as it searches for their data
Range("G6:G286").Select 'go to the column in the table that holds the names
Cells.Find(What:=N, After:=Range("G6"), LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate 'find the current Team in the table
ActiveCell.Select
ActiveCell.Offset(11, 66).Select 'select the 3 mo moving avg. for that Team
For Z = 0 To 15
MovingExtract(E, Z) = Selection 'load up array with values for each person
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(0, -16).Select
E = E + 1
Application.Goto Reference:="Bookmark" 'go back to the list of Teams on the data page

ActiveCell.Offset(1, 0).Activate 'move down one cell

Loop 'loop to the next Team in the list

Sheets("Extract Averages").Activate
Range("Bookmark2").Select
ActiveCell.Offset(0, 1).Activate

For i = 0 To E
For Z = 0 To 15
ActiveCell.Offset(0, Z) = MovingExtract(i, Z)
Next
ActiveCell.Offset(1, 0).Activate 'move down one cell
Next 'i

End Sub

HTH
--
RMC,CPA


"Neal Zimm" wrote in message ...
Hi

example A
Dim A As Variant
A = Array(10,20,30)
B = A(2)
the above is straight from Excel help on array functions.

If have used two dimension arrays like this befo
example B
dim stuff(20,20) as integer

where stuff( var1, var2) gets me to where I want.

Q1) Can you 'do' exampleA in two dimensions?

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Thanks much,

--
Neal Z

Array function, two dimensions?? and worksheet arrays
 

Q1) Can you 'do' exampleA in two dimensions?

Yes, if you Dim the 2-D array as a variant. Only variants can hold
arrays... even arrays of arrays.

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?

No. Only values can be stored using the "Array" identifier. However, you
can store objects that are "pointers" to values. For example,

Dim arr(0 To 1) As Variant 'Variant array
Dim i As Integer
Dim j As Integer

arr(0) = Array(Range("a1"), Range("b2"), Range("c3")) 'Range of cells,
arr(1) = Array(Range("a2"), Range("b3"), Range("c4")) 'not their values

For i = 0 To 1
For j = 0 To 2
Debug.Print arr(i)(j).Value 'This works! Boy, was I surprised!
Next j
Next i

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Not quite sure what you mean here. Are you talking about array formulas in
Excel? Lookup the FormulaArray property in VBA help. Note that you have to
use R1C1 nomenclature in the formulae in this instance.

Array function, two dimensions?? and worksheet arrays
 

Q1) Can you 'do' exampleA in two dimensions?
No

You can do an array of arrays to simulate two dimensions, but you can't
create a two dimensional array in one command using the Array function.

You can do

Dim A as Variant
A = Evaluate("{1,2,3;4,5,6;7,8,9;10,11,12}")

will initialize a 4 x 3 array

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?
Yes
demo'd from the immediate window
f = 3
j = ll
h = 12
A = Array(f, j, h)
? a(2)
12
? a(0)
3


Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

ActiveCell.Formula = "=Sum(A1:A10,C1:C10)"

or
set rng = Range("A1:A0,C1:C10")
ActiveCell.Formula = "=Sum(" & rng.Address & ")"


--
Regards,
Tom Ogilvy


"Neal Zimm" wrote in message
...
Hi

example A
Dim A As Variant
A = Array(10,20,30)
B = A(2)
the above is straight from Excel help on array functions.

If have used two dimension arrays like this befo
example B
dim stuff(20,20) as integer

where stuff( var1, var2) gets me to where I want.

Q1) Can you 'do' exampleA in two dimensions?

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing
values?

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Thanks much,

--
Neal Z

Array function, two dimensions?? and worksheet arrays
 

Thanks so much, I'll start to look at it.
--
Neal Z


"R. Choate" wrote:

Here is an example from "real life". I did not take anything out of this to make it easier to understand, so you may need to study
it for some time. This is a 2-dimension array example and it will show you how to load up both dimensions with variables from the
workbook.

Sub GetTeamExtract()
Dim MovingExtract(100, 16) As Variant
Dim E As Long
Dim Z As Long

Sheets("Team Data Table").Activate
Range("B7").Activate
E = 0
Do Until ActiveCell = "" 'loop through the list of Teams and copy their data
'to their data table

ActiveWorkbook.Names.Add Name:="Bookmark", RefersTo:=ActiveCell
N = ActiveCell.Text 'set variable to = Team names as it searches for their data
Range("G6:G286").Select 'go to the column in the table that holds the names
Cells.Find(What:=N, After:=Range("G6"), LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False).Activate 'find the current Team in the table
ActiveCell.Select
ActiveCell.Offset(11, 66).Select 'select the 3 mo moving avg. for that Team
For Z = 0 To 15
MovingExtract(E, Z) = Selection 'load up array with values for each person
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(0, -16).Select
E = E + 1
Application.Goto Reference:="Bookmark" 'go back to the list of Teams on the data page

ActiveCell.Offset(1, 0).Activate 'move down one cell

Loop 'loop to the next Team in the list

Sheets("Extract Averages").Activate
Range("Bookmark2").Select
ActiveCell.Offset(0, 1).Activate

For i = 0 To E
For Z = 0 To 15
ActiveCell.Offset(0, Z) = MovingExtract(i, Z)
Next
ActiveCell.Offset(1, 0).Activate 'move down one cell
Next 'i

End Sub

HTH
--
RMC,CPA


"Neal Zimm" wrote in message ...
Hi

example A
Dim A As Variant
A = Array(10,20,30)
B = A(2)
the above is straight from Excel help on array functions.

If have used two dimension arrays like this befo
example B
dim stuff(20,20) as integer

where stuff( var1, var2) gets me to where I want.

Q1) Can you 'do' exampleA in two dimensions?

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Thanks much,

--
Neal Z

Array function, two dimensions?? and worksheet arrays
 

Thanks, it's what the 'doctor' ordered.
--
Neal Z


"Eric White" wrote:

Q1) Can you 'do' exampleA in two dimensions?

Yes, if you Dim the 2-D array as a variant. Only variants can hold
arrays... even arrays of arrays.

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?

No. Only values can be stored using the "Array" identifier. However, you
can store objects that are "pointers" to values. For example,

Dim arr(0 To 1) As Variant 'Variant array
Dim i As Integer
Dim j As Integer

arr(0) = Array(Range("a1"), Range("b2"), Range("c3")) 'Range of cells,
arr(1) = Array(Range("a2"), Range("b3"), Range("c4")) 'not their values

For i = 0 To 1
For j = 0 To 2
Debug.Print arr(i)(j).Value 'This works! Boy, was I surprised!
Next j
Next i

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Not quite sure what you mean here. Are you talking about array formulas in
Excel? Lookup the FormulaArray property in VBA help. Note that you have to
use R1C1 nomenclature in the formulae in this instance.

Array function, two dimensions?? and worksheet arrays
 

As always Tom,
Concise, to the point, clever and accurate.
Thanks.
How can I get in touch with you outside of this bulletin board?
Snail mail or otherwise?
Many Thanks,
--
Neal Z


"Tom Ogilvy" wrote:

Q1) Can you 'do' exampleA in two dimensions?
No

You can do an array of arrays to simulate two dimensions, but you can't
create a two dimensional array in one command using the Array function.

You can do

Dim A as Variant
A = Evaluate("{1,2,3;4,5,6;7,8,9;10,11,12}")

will initialize a 4 x 3 array

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing values?
Yes
demo'd from the immediate window
f = 3
j = ll
h = 12
A = Array(f, j, h)
? a(2)
12
? a(0)
3


Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

ActiveCell.Formula = "=Sum(A1:A10,C1:C10)"

or
set rng = Range("A1:A0,C1:C10")
ActiveCell.Formula = "=Sum(" & rng.Address & ")"


--
Regards,
Tom Ogilvy


"Neal Zimm" wrote in message
...
Hi

example A
Dim A As Variant
A = Array(10,20,30)
B = A(2)
the above is straight from Excel help on array functions.

If have used two dimension arrays like this befo
example B
dim stuff(20,20) as integer

where stuff( var1, var2) gets me to where I want.

Q1) Can you 'do' exampleA in two dimensions?

Q2) the Excel text for example A says that 10,20,30 arglist are values.
Can what's inside the parenthesis be variable names containing
values?

Q3) New topic:
I can't seem to find an example of how to specify an array of cells
for worksheet formulas (NOT a vba array)
will you provide a brief example?

Thanks much,

--
Neal Z

Array function, two dimensions?? and worksheet arrays