Programming Question

[Mac Lingo]

In VB, I want to load an array and then assign that array to a row in a
designated sheet.

Question 1: How do you assign an array into a Row in a Sheet?
Sheets("Name").??? = ROW_ARRAY

Question 2: What form does RowData have to be? Is a one dimension matrix or
a two dimension matrix or something else?

ROW_ARRAY can have two forms: either ROW_ARRAY(a number) or ROW_ARRAY(). Is
there different coding for these two definitions?

Thanks for your help,
Capt Mac

 

[Dave Peterson]

In general, you can assign an array to a range like:

Option Explicit
Sub testme03()

Dim myArr() As Variant
Dim iCtr As Long
Dim jCtr As Long

ReDim myArr(-5 To 5, 3 To 10)

For iCtr = LBound(myArr, 1) To UBound(myArr, 1)
For jCtr = LBound(myArr, 2) To UBound(myArr, 2)
myArr(iCtr, jCtr) = iCtr & "---" & jCtr
Next jCtr
Next iCtr

ActiveSheet.Range("a15").Resize(UBound(myArr, 1) - LBound(myArr, 1) + 1, _
UBound(myArr, 2) - LBound(myArr, 2) + 1).Value _
= myArr

End Sub

But specifically, if you use a two dimensional array:

Sub testme01()

Dim iCtr As Long
Dim myArr(1 To 1, 1 To 8) As Variant
'one row by 8 columns.

For iCtr = LBound(myArr, 2) To UBound(myArr, 2)
myArr(1, iCtr) = iCtr
Next iCtr

ActiveSheet.Range("a1:h1").Value = myArr

End Sub

or a one dimensional array

Sub testme02()

Dim iCtr As Long
Dim myArr(1 To 8) As Variant
'one row by 8 columns.

For iCtr = LBound(myArr) To UBound(myArr)
myArr(iCtr) = iCtr
Next iCtr

ActiveSheet.Range("a1:h1").Value = myArr

End Sub

If you have a one dimensional array and want to populate a column of cells:

ActiveSheet.Range("a1:A8").Value = Application.Transpose(myArr)

(In versions earlier than xl2002, that array can't exceed 5461
elements--application.transpose has trouble with a larger number of elements.)

 

[Mac Lingo]

If you have an array
Dim ARRAY(2,5)
you can easily place it in an Worksheet as follows:
RANGE_STR = "A1:E2" ' Note same arrangement as ARRAY
WorkSheet.Range(RANGE_STR).Value = ARRAY

Capt Mac

 

[Chip Pearson]

Dim ARRAY(2,5)

You can't use "Array" as a variable name; it is a reserved word
in VBA.



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com