Assigning values from a sheet to an array

[Anne-Marie]

Can anyone explain why I get a runtime error with the code below.MANY
THANKS!

Option Explicit
Option Base 1


Sub testing3()
Dim Vt() As Double
Dim j As Long
Dim i As Long

ReDim Preserve Vt(1 To 40)
For i = 1 To 40
j = Int(Rnd() * 1100)
Vt(i) = Worksheets("data").Cells(1, j)
Next i
End Sub

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Alan Beban]

Because j is greater than 256, the maximum number of columns in Excel.

Alan Beban

 

[Anne-Marie]

Thanks for your reply.

I tried changing the 110 0 to 10 and I still get the same error:
Application-defined or object defined error in the line starting V(i)

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Frank Kabel]

Hi
beacuse you could get a column index of zero. Try
j = Int(Rnd() * 10)+1