Prev: array values
Next: Copy the contents of a range of cells to a single comment (indicator)?
From: Striker3070 on 29 Apr 2010 17:34
I have an array with about 1500 names in it. How can I add those names to
column A1 on sheet1 and go down one cell 1500 times and add the next value
to the spreadsheet? in Excel07 VBA

From: Striker3070 on 29 Apr 2010 17:43
apologize for duplicate post, seem to have connectivity issues

"Striker3070" <striker3070(a)qwest.net> wrote in message
news:F0806BFF-7975-4C06-A448-37FFDBEB9DE4(a)microsoft.com...
> I have an array with about 1500 names in it. How can I add those names to
> column A1 on sheet1 and go down one cell 1500 times and add the next value
> to the spreadsheet? in Excel07 VBA

From: Dave Peterson on 29 Apr 2010 18:08
Since you're only using about 1500 names, you should be able to plop those names
back into the worksheet in one fell swoop:

Option Explicit
Sub testme()

Dim myArr As Variant
Dim myCell As Range

myArr = Array(1, 2, 3, "abc", "def")

Set myCell = ActiveSheet.Range("A1")

myCell.Resize(UBound(myArr) - LBound(myArr) + 1, 1).Value _
= Application.Transpose(myArr)

End Sub

Some versions of excel (before xl2002???) had trouble with over 7000 elements (I
forget the exact number).

But you could loop with something like:

Option Explicit
Sub testme2()

Dim myArr As Variant
Dim myCell As Range
Dim iCtr As Long

myArr = Array(1, 2, 3, "abc", "def")

Set myCell = ActiveSheet.Range("A1")

For iCtr = LBound(myArr) To UBound(myArr)
myCell.Value = myArr(iCtr)
Set myCell = myCell.Offset(1, 0)
Next iCtr

End Sub


Striker3070 wrote:
>
> I have an array with about 1500 names in it. How can I add those names to
> column A1 on sheet1 and go down one cell 1500 times and add the next value
> to the spreadsheet? in Excel07 VBA

--

Dave Peterson
 | 
Pages: 1
Prev: array values
Next: Copy the contents of a range of cells to a single comment (indicator)?