arrays

  • Thread starter Thread starter Striker3070
  • Start date Start date
S

Striker3070

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
 
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
 
Back
Top