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.

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.

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.

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.

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.

This is an example from a book. I did it this way routinely before. But
not today!

He does:

' Transfer temporary array to worksheet
TheRange.Value = TempArray

I did:
Range("b2", "b501") = NPV

..Value seems not the problem, but I got all the first elemen!

Thanks.

**********************************************************************************************

Sub ArrayFillRange()
' Fill a range by transferring an array

Dim TempArray() As Integer
Dim TheRange As Range

' Get the dimensions
CellsDown = Val(InputBox("How many cells down?"))
CellsAcross = Val(InputBox("How many cells across?"))

' Record starting time
StartTime = Timer

' Redimension temporary array
ReDim TempArray(1 To CellsDown, 1 To CellsAcross)

' Set worksheet range
Set TheRange = ActiveCell.Range(Cells(1, 1), _
Cells(CellsDown, CellsAcross))

' Fill the temporary array
CurrVal = 0
Application.ScreenUpdating = False
For i = 1 To CellsDown
For j = 1 To CellsAcross
TempArray(i, j) = CurrVal + 1
CurrVal = CurrVal + 1
Next j
Next i

' Transfer temporary array to worksheet
TheRange.Value = TempArray

' Display elapsed time
Application.ScreenUpdating = True
MsgBox Format(Timer - StartTime, "00.00") & " seconds"
End Sub

****************************************************************************************
Ed wrote:
> 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.

You've got me all confused! I copied and ran the sample Sub you posted
and it worked great for me in XL XP. Then I tried my own, and it
bombed! I dunno - I hope one of the experts comes along and
straightens us both out!

Ed

Sub Test_Array2Range()

Dim arr(6) As String
Dim rng As Range
Dim x As Long
Dim wkb As Workbook
Dim wks As Worksheet

Set wkb = ActiveWorkbook
Set wks = wkb.Sheets(1)
Set rng = wks.Range("B2:B7")

For x = 0 To 5
arr(x) = "Step " & x + 1
Next x

rng.Value = arr

End Sub

muster wrote:
> This is an example from a book. I did it this way routinely before. But
> not today!
>
> He does:
>
> ' Transfer temporary array to worksheet
> TheRange.Value = TempArray
>
> I did:
> Range("b2", "b501") = NPV
>
> .Value seems not the problem, but I got all the first elemen!
>
> Thanks.
>
> **********************************************************************************************
>
> Sub ArrayFillRange()
> ' Fill a range by transferring an array
>
> Dim TempArray() As Integer
> Dim TheRange As Range
>
> ' Get the dimensions
> CellsDown = Val(InputBox("How many cells down?"))
> CellsAcross = Val(InputBox("How many cells across?"))
>
> ' Record starting time
> StartTime = Timer
>
> ' Redimension temporary array
> ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
>
> ' Set worksheet range
> Set TheRange = ActiveCell.Range(Cells(1, 1), _
> Cells(CellsDown, CellsAcross))
>
> ' Fill the temporary array
> CurrVal = 0
> Application.ScreenUpdating = False
> For i = 1 To CellsDown
> For j = 1 To CellsAcross
> TempArray(i, j) = CurrVal + 1
> CurrVal = CurrVal + 1
> Next j
> Next i
>
> ' Transfer temporary array to worksheet
> TheRange.Value = TempArray
>
> ' Display elapsed time
> Application.ScreenUpdating = True
> MsgBox Format(Timer - StartTime, "00.00") & " seconds"
> End Sub
>
> ****************************************************************************************
> Ed wrote:
> > 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.

Hi Muster,

Try:

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

---
Regards,
Norman


"muster" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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" <[email protected]> wrote in message
news:[email protected]...
> Hi Muster,
>
> Try:
>
> Range("B2").Resize(UBound(NPV) - LBound(NPV) + 1).Value _
> = Application.Transpose(NPV)
>
> ---
> Regards,
> Norman

Got it.

rng = application.transpose(arr)

Thank Norman.

Ed wrote:
> You've got me all confused! I copied and ran the sample Sub you posted
> and it worked great for me in XL XP. Then I tried my own, and it
> bombed! I dunno - I hope one of the experts comes along and
> straightens us both out!
>
> Ed
>
> Sub Test_Array2Range()
>
> Dim arr(6) As String
> Dim rng As Range
> Dim x As Long
> Dim wkb As Workbook
> Dim wks As Worksheet
>
> Set wkb = ActiveWorkbook
> Set wks = wkb.Sheets(1)
> Set rng = wks.Range("B2:B7")
>
> For x = 0 To 5
> arr(x) = "Step " & x + 1
> Next x
>
> rng.Value = arr
>
> End Sub
>
> muster wrote:
> > This is an example from a book. I did it this way routinely before. But
> > not today!
> >
> > He does:
> >
> > ' Transfer temporary array to worksheet
> > TheRange.Value = TempArray
> >
> > I did:
> > Range("b2", "b501") = NPV
> >
> > .Value seems not the problem, but I got all the first elemen!
> >
> > Thanks.
> >
> > **********************************************************************************************
> >
> > Sub ArrayFillRange()
> > ' Fill a range by transferring an array
> >
> > Dim TempArray() As Integer
> > Dim TheRange As Range
> >
> > ' Get the dimensions
> > CellsDown = Val(InputBox("How many cells down?"))
> > CellsAcross = Val(InputBox("How many cells across?"))
> >
> > ' Record starting time
> > StartTime = Timer
> >
> > ' Redimension temporary array
> > ReDim TempArray(1 To CellsDown, 1 To CellsAcross)
> >
> > ' Set worksheet range
> > Set TheRange = ActiveCell.Range(Cells(1, 1), _
> > Cells(CellsDown, CellsAcross))
> >
> > ' Fill the temporary array
> > CurrVal = 0
> > Application.ScreenUpdating = False
> > For i = 1 To CellsDown
> > For j = 1 To CellsAcross
> > TempArray(i, j) = CurrVal + 1
> > CurrVal = CurrVal + 1
> > Next j
> > Next i
> >
> > ' Transfer temporary array to worksheet
> > TheRange.Value = TempArray
> >
> > ' Display elapsed time
> > Application.ScreenUpdating = True
> > MsgBox Format(Timer - StartTime, "00.00") & " seconds"
> > End Sub
> >
> > ****************************************************************************************
> > Ed wrote:
> > > 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.