Prev: Enable Spelling Check
Next: arrays
From: Striker3070 on 29 Apr 2010 17:19
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: JLGWhiz on 29 Apr 2010 18:56
I think this is right. give it a try. Substiture your array name for
myArr. The code below assumes a zero based array, if yours is base 1 then
you will not need the + 1 for the range row designation. This would start in
A1 and continue downward for the number of items in the array.

For i = LBound(myArr) To UBound(myArr)
Range("A" & i + 1) = myArr(i).value
Next





"Striker3070" <striker3070(a)qwest.net> wrote in message
news:A8A6DCC6-327A-4F56-BFE0-E943989F5226(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: JLGWhiz on 29 Apr 2010 19:05
Should have tested it first. Use this.

For i = LBound(myArr) To UBound(myArr)
Range("A" & i + 1) = myArr(i)
Next

Adding the .Value will throw an error since myArr(i) is a value.


"Striker3070" <striker3070(a)qwest.net> wrote in message
news:A8A6DCC6-327A-4F56-BFE0-E943989F5226(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: Dana DeLouis on 30 Apr 2010 00:31
On 4/29/2010 7:05 PM, JLGWhiz wrote:
> Should have tested it first. Use this.
>
> For i = LBound(myArr) To UBound(myArr)
> Range("A"& i + 1) = myArr(i)
> Next
>
> Adding the .Value will throw an error since myArr(i) is a value.
>
>
> "Striker3070"<striker3070(a)qwest.net> wrote in message
> news:A8A6DCC6-327A-4F56-BFE0-E943989F5226(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

> For i = LBound(myArr) To UBound(myArr)

Hi. Not likely, but one could have:

Dim m(-5 To 5)

Just an idea...

n = UBound(v) - LBound(v) + 1

[A1].Resize(n) = v
'or
[B1].Resize(n) = WorksheetFunction.Transpose(v)

= = = = = = =
HTH :>)
Dana DeLouis
From: Javed on 30 Apr 2010 05:02
On Apr 30, 9:31 am, Dana DeLouis <delo...(a)bellsouth.net> wrote:
> On 4/29/2010 7:05 PM, JLGWhiz wrote:
>
> > Should have tested it first.  Use this.
>
> > For i = LBound(myArr) To UBound(myArr)
> >    Range("A"&  i + 1) = myArr(i)
> > Next
>
> > Adding the .Value will throw an error since myArr(i) is a value.
>
> > "Striker3070"<striker3...(a)qwest.net>  wrote in message
> >news:A8A6DCC6-327A-4F56-BFE0-E943989F5226(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
>
>  > For i = LBound(myArr) To UBound(myArr)
>
> Hi.  Not likely, but one could have:
>
> Dim m(-5 To 5)
>
> Just an idea...
>
> n = UBound(v) - LBound(v) + 1
>
> [A1].Resize(n) = v
> 'or
> [B1].Resize(n) = WorksheetFunction.Transpose(v)
>
> = = = = = = =
> HTH  :>)
> Dana DeLouis

Use following code:

Range(YourRange).value=worksheetfunction.transpose(MyArr)
 | 
Pages: 1
Prev: Enable Spelling Check
Next: arrays