paste values from an array to a range

[Nick_F]

Hi,

I can't get this code to work, I get the first element of the array
pasted to the range when I want all the elements to be pasted to the
range. I took this code from another script I wrote which works fine.
I just can't understand what is happening. When I check the locals
window the expected values are in the array.

So the array might contain 2, 4, 6, ... but the range will contain
only 2, 2, 2, ....


Sub work_dammit()

Freq = Range("L1").Value
j = Application.Count(Range("B:B"))
ReDim Array_Time(1 To j) As Variant
For i = 1 To j
Array_Time(i) = i * SRM_Freq
Next i

Range("A1:A" & j).Value = Array_Time

End Sub

Can anyone enlighten me.

thanks
nick

 

[Gary Keramidas]

not sure how you get your values, so i simulated it this way.

array_time = Array(2, 4, 6)
For i = LBound(array_time) To UBound(array_time)

Range("a" & i + 1) = array_time(i)
Next

 

[Gary Keramidas]

forgot to multiply by your freq

Range("a" & i + 1) = array_time(i) * SRM_Freq

 

[Jim Cone]

nick,
You should declare your variables.
Also, strongly suggest you add Option Explicit at the top of the module.
Your array is a horizontal array and it needs to be changed to a vertical array.
'--
Sub work_dammit()
Dim Freq As Double
Dim Array_Time() As Variant
Dim j As Long
Dim i As Long
Const SRM_Freq As Long = 10

Freq = Range("L1").Value
j = Application.Count(Range("B:B"))
ReDim Array_Time(1 To j)
For i = 1 To j
Array_Time(i) = i * SRM_Freq
Next i
Range("A1:A" & j).Value = Application.Transpose(Array_Time)
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Nick_F" <[email protected]>
wrote in message
Hi,

I can't get this code to work, I get the first element of the array
pasted to the range when I want all the elements to be pasted to the
range. I took this code from another script I wrote which works fine.
I just can't understand what is happening. When I check the locals
window the expected values are in the array.

So the array might contain 2, 4, 6, ... but the range will contain
only 2, 2, 2, ....
Sub work_dammit()
Freq = Range("L1").Value
j = Application.Count(Range("B:B"))
ReDim Array_Time(1 To j) As Variant
For i = 1 To j
Array_Time(i) = i * SRM_Freq
Next i
Range("A1:A" & j).Value = Array_Time
End Sub
Can anyone enlighten me.
thanks
nick

 

[Nick_F]

Thanks Jim and Gary,

Those suggestions helped solve the problem. Coming from a R/Matlab
background I forget that Excel works in rows while I prefer columns.
Sadly you replied too quickly so now I find myself with work to do for
the rest of the Friday afternoon when I could have snuck away for some
golf!

Have a good weekend
nick