write array to range
 

Hi all,

I'm picking up Excel VBA again and have some trouble with this:

Range("b2", "b501") = NPV,

where NPV is an array and it looks OK, but the output is all the same,
500 of the first element, what's wrong?

Thank you.

write array to range
 

I'm not a great expert, so someone who is may have a better answer.
But I think you have to identify which element of the array you want to
write. If I had something like this, I'd probably do something like:

Dim x As Long
For x = 0 To UBound(NPV)
Range("B" & x+2) = NPV(x)
Next x

(This assumes you did not set Option Base 1 to start your array count
at 1, and it is set as default to write the first term to NPV(0). If
you did start at 1, then use
For x = 1 To UBound(NPV)
Range("B" & x+1) = NPV(x)

Ed

muster wrote:
Hi all,

I'm picking up Excel VBA again and have some trouble with this:

Range("b2", "b501") = NPV,

where NPV is an array and it looks OK, but the output is all the same,
500 of the first element, what's wrong?

Thank you.

write array to range
 

Thank you Ed. Your way surely works. The way I asked suppose to be a
faster way to write an array to a range, that is to say, if it works.

Thank you and more hints please.


Ed wrote:
I'm not a great expert, so someone who is may have a better answer.
But I think you have to identify which element of the array you want to
write. If I had something like this, I'd probably do something like:

Dim x As Long
For x = 0 To UBound(NPV)
Range("B" & x+2) = NPV(x)
Next x

(This assumes you did not set Option Base 1 to start your array count
at 1, and it is set as default to write the first term to NPV(0). If
you did start at 1, then use
For x = 1 To UBound(NPV)
Range("B" & x+1) = NPV(x)

Ed

muster wrote:
Hi all,

I'm picking up Excel VBA again and have some trouble with this:

Range("b2", "b501") = NPV,

where NPV is an array and it looks OK, but the output is all the same,
500 of the first element, what's wrong?

Thank you.

write array to range
 

muster wrote:
Thank you Ed. Your way surely works.

I'm glad I could help. It feels good to finally be on the giving end.

The way I asked suppose to be a
faster way to write an array to a range, that is to say, if it works.

I'm not familiar enough to know all the faster ways to do things. I'm
still doing a lot of cave-man stuff myself. But it doesn't seem likely
that you can match a collection of data (which is what an array is) to
a range of cells - even though the range is made up of many cells, the
range itself is one single block. Maybe it can be done, but it strikes
me as like throwing a deck of cards at the box.

more hints please.

I wish I had more! <g My own code would probably look better!

Ed



Ed wrote:
I'm not a great expert, so someone who is may have a better answer.
But I think you have to identify which element of the array you want to
write. If I had something like this, I'd probably do something like:

Dim x As Long
For x = 0 To UBound(NPV)
Range("B" & x+2) = NPV(x)
Next x

(This assumes you did not set Option Base 1 to start your array count
at 1, and it is set as default to write the first term to NPV(0). If
you did start at 1, then use
For x = 1 To UBound(NPV)
Range("B" & x+1) = NPV(x)

Ed

muster wrote:
Hi all,

I'm picking up Excel VBA again and have some trouble with this:

Range("b2", "b501") = NPV,

where NPV is an array and it looks OK, but the output is all the same,
500 of the first element, what's wrong?

Thank you.

write array to range
 

Hi Muster,

Try:

Range("B2").Resize(UBound(NPV) - LBound(NPV) + 1).Value _
= Application.Transpose(NPV)

---
Regards,
Norman


"muster" wrote in message
oups.com...
Hi all,

I'm picking up Excel VBA again and have some trouble with this:

Range("b2", "b501") = NPV,

where NPV is an array and it looks OK, but the output is all the same,
500 of the first element, what's wrong?

Thank you.

write array to range
 

Hi Muster,

Just to add:

'=============
Public Sub TesterD01()
Dim NPV(1 To 500)
Dim i As Long

For i = 1 To 500
NPV(i) = i
Next i

Range("B2").Resize(UBound(NPV) - LBound(NPV) + 1).Value _
= Application.Transpose(NPV)
End Sub
'<<=============


---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Muster,

Try:

Range("B2").Resize(UBound(NPV) - LBound(NPV) + 1).Value _
= Application.Transpose(NPV)

---
Regards,
Norman