Array Element question

[S G Booth]

How can I refer to elements in an array?
Say just the 1st 20, and paste the values into
"A1" of the activesheet, please?

What if there were less than 20 elements?

Regards.

 

[Tom Ogilvy]

Assume a 1 dimensional array

Sub Arr1()
Dim myArray()
Dim num As Long
num = 5
ReDim myArray(0 To num - 1)
For i = 0 To num - 1
myArray(i) = i
Next
lastrow = 20
If UBound(myArray) - LBound(myArray) + 1 < 20 Then _
lastrow = UBound(myArray) - LBound(myArray) + 1
Range("A1:A" & lastrow).Value = Application.Transpose(myArray)
End Sub


If more than 20 values, then only the first 20 values will appear.

 

[S G Booth]

Many thanks.
Should have said that the array elements need to paste
with a blank row between each element, rather than as a
contiguous 'list'.

I will be pasting them into a sheet where there will be 45+
designated rows. Any current values can be overwritten. I don't want to
insert the values (creating extra rows), just to overwrite the existing
contents.

Looked in Help to see if there is an argument with
Transpose that might be useful....but there doesn't seem
to be a listing for Transpose.

Can I loop through the 20 elements one at a time, and paste each time with
an offset, ....or a better way?

Regards and thanks.

 

[Tom Ogilvy]

Should have said that the array elements need to paste

with a blank row between each element, rather than as a
contiguous 'list'.

That would have been useful.

Dim i as Long, j as Long, k as Long
Dim rng as Range
' set rng to first cell to hold value
set rng = Worksheets("ABC").Range("F32")
i = -1
j = 0
for k = lbound(vArr) to ubound(vArr)
i = i + 2: j = j + 1
rng(i).Value = vArr(k)
if j >= 20 then exit for
Next

code is untested, but should represent a feasible approach.

 

[S G Booth]

Sorry for that omission. Didn't know Transpose.
Ignorantly, I assumed any reply would pull out the
array elements one at a time (and I could then try
to adapt the code).

Thanks for the approach. Will try to develop it.

Regards.

 

[Guest]

To answer your second question...

This will rename the first worksheet to 'Test'

Sheet1.Name = "Test"