array question, referenceing

[Al]

Hey all

I have 2 arrays called arr1 and arr2 (string arrays)

arr1 contains the values 1, 2, 3
arr2 contains the values a, b, c

I have a third array, namesarr (also String), which contains the names
of the first 2 arrays

I am trying to have the following output on my excell sheet

arr1
1
2
3
arr2
a
b
c

Here is the code I am using:

For S = 0 To UBound(namesarr)
ActiveCell.Value = namesarr(S)

For Each element In Array(namesarr(S))
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = element
Next element
Next S

here is what I get:

arr1
arr2

The contents of each of my arrays don't get printed. I am sure that I
am referencing them incorrectly, but I can't figure it out. any help
appreciated. Thanks

Al

 

[Guest]

try this

myoffset = 1
For S = 0 To UBound(namesarr)
ActiveCell.Value.offset(myoffset,0) = namesarr(S)
myoffset = myoffset + 1

For Each element In Array(namesarr(S))
ActiveCell.Value.offset(myoffset,0) = element
myoffset = myoffset + 1
Next element
Next S

 

[Al]

Thanks for the help, but your code prodiced no output at all... any
other ideas?

 

[Guest]

myoffset = 1
Try removing value. It really belongs at the end.

For S = 0 To UBound(namesarr)
ActiveCell.offset(myoffset,0) = namesarr(S)
myoffset = myoffset + 1

For Each element In Array(namesarr(S))
ActiveCell.offset(myoffset,0) = element
myoffset = myoffset + 1
Next element
Next S

 

[Al]

Still nothing...

I am quite certain that the line:

For Each element In Array(namesarr(S))

is incorrect. when I do debug and hold my mouse over
"Array(namesarr(S))" I get a tooltip that says namesarr(S)="arr1"

the arr1 in this case (I surmise) is a word, or a variable, NOT the
array called arr1.

How do I properly refer to the array arr1?

 

[Guest]

Remove parethesis

For Each element In Array(namesarr)

 

[Dave Peterson]

Are you looking for something like:

arr1 1
2
arr2 a
b
c

The array names in one column and the elements in the column to the right.

If yes:

Option Explicit
Sub testme01()

Dim arr1 As Variant
Dim arr2 As Variant
Dim NamesArr As Variant
Dim RealArr As Variant
Dim iCtr As Long
Dim jCtr As Long
Dim DestCell As Range

arr1 = Array(1, 2, 3)
arr2 = Array("a", "b", "c")

'make sure both of these have the same number of elements!
NamesArr = Array("arr1", "arr2")
RealArr = Array(arr1, arr2)

Set DestCell = ActiveCell
For iCtr = LBound(NamesArr) To UBound(NamesArr)
DestCell.Value = NamesArr(iCtr)
'move over for the first element of the array
Set DestCell = DestCell.Offset(0, 1)
For jCtr = LBound(RealArr(iCtr)) To UBound(RealArr(iCtr))
DestCell.Value = RealArr(iCtr)(jCtr)
'get ready for the next element
Set DestCell = DestCell.Offset(1, 0)
Next jCtr
'come back to original column for next array
Set DestCell = DestCell.Offset(-1, -1)
Next iCtr
End Sub

I'm not sure if NamesArr contained the names of the arrays or the actual
arrays. But in any case, I don't think you're going to use the string as a
variable.

If you did:
Dim NamesArr as Variant
NamesArr = array("arr1","arr2")

Then when you're in the loop, this line:

For Each element In Array(namesarr(S))
is essentially this:
For each element in array("arr1")
and then
For each element in array("arr2")

In either case, you're looping through an array with just one element -- "arr1"
the first time and "arr2" the second time.