icemail
01-12-2020, 11:33 AM
Hello i try to create array from cell. But when code coming to "MsgBox myarr(randno)" its giving "'Run-time error '9': Subscript out of range". How can we solved any idea?
Sub randarr
Dim myarr As Variant
Dim myrange As Range
Dim randno As Long
Set myrange = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
myarr = myrange
randno = Application.WorksheetFunction.RandBetween(LBound(myarr), UBound(myarr))
MsgBox myarr(randno) 'Run-time error '9': Subscript out of range
End Sub
Sub randarr
Dim myarr As Variant
Dim myrange As Range
Dim randno As Long
Set myrange = ActiveSheet.Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
myarr = myrange
randno = Application.WorksheetFunction.RandBetween(LBound(myarr), UBound(myarr))
MsgBox myarr(randno) 'Run-time error '9': Subscript out of range
End Sub