Excel Run-Time Error '9' issue.

[rbg0519]

I'm working with MS-Excel VBA Programming for the absolute beginner and I'm having difficulty creating the code for this particular challenge.

I'm tasked with creating 25 random numbers from 0-99 and then placing the values returned into an array which will be displayed alongside the numbers generated. However, I've clearly done it incorrectly and am receiving a Run-Time error '9': stating "Subscript out of range"

I'm using Excel 6.5 and windows XP.

Here is my code. (Forgive my random dim statements which are apparently not used properly, or at all).


Dim intTwoDimArray(4, 4) As Integer
Dim objMyRange As Object
Dim objMyCell As Object
Dim strArray(1, 9) As String
Dim intArrayRange As Object
Dim intColumnIndex As Integer
Dim intRowIndex As Integer


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set objMyRange = Range("A1:E5")
For Each objMyCell In objMyRange
objMyCell = Int(Rnd * 100) + 1
Next objMyCell

Set intArrayRange = Range("F1:F25")
For intRowIndex = 0 To 1
For intColumnIndex = 0 To 9
strArray(intRowIndex, intColumnIndex) = Cells(intRowIndex + 1, Chr(65 + intColumnIndex))
Next intColumnIndex
Next intRowIndex
Cells((intColumnIndex + (intRowIndex * 4)) + 1, "F").Value = intTwoDimArray(intRowIndex, intColumnIndex)
End Sub


Highlighted in Red at the bottom is where the code seems to be hanging up, or is flagged under the debug.

Any help would be greatly appreciated!!

Thanks in advance,

b-dub

 

[VoG]

Not sure but perhaps

Cells(intColumnIndex + intRowIndex * 4 + 1, "F").Value = intTwoDimArray(intRowIndex, intColumnIndex)

If not, please describe what the code is meant to do.

Error 9 indicates that a range does not exist so check what these values are:

intColumnIndex + intRowIndex * 4 + 1, intRowIndex and intColumnIndex

 

[rbg0519]

Thanks for your response VoG:

The code is designed to create 25 random numbers (Between 0-99) in cells A1-E5 (25 cells).

Then place the values of the cells (randomly generated numbers) and place them into an Array which should then be displayed from Cells F1 to F25.


Also, tried your changes to the code to no avail.

 

[VoG]

Try this

Sub Randoms()
Dim i As Long, j As Long, Arr
Randomize
For i = 1 To 5
    For j = 1 To 5
        Cells(i, j).Value = Int(Rnd * 100) + 1
    Next j
Next i
Arr = Range("A1:E5")
Range("F1").Resize(5, 5).Value = Application.Transpose(Arr)
End Sub

 

[rbg0519]

Hm..No luck.

With the existing code, i've successfully generated the 25 numbers,
based on the test i've run on the back end following each line of code as it is executed and prompting each execution with F8 key, The numbers are being loaded into an array (or so i think) but are not reaching their output destination on the front end.

 

[VoG]

Hm..No luck.

What does that mean? My result

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	89	18	76	62	39	89	48	24	64	35
2	48	70	50	96	41	18	70	18	32	38
3	24	18	24	87	17	76	50	24	41	61
4	64	32	41	48	21	62	96	87	48	89
5	35	38	61	89	35	39	41	17	21	35
Sheet8

 

[rbg0519]

Ah forgive me, I left this under a private sub worksheet.

That is exactly what i need it to do, only I need to get the return values in cells F1-F25, as opposed to F1-J5

 

[danicalhoun]

Hi I am working on this same problem, and I came across this thread. Did you guys ever figure out how to get the values to F1-F25? I don't know what I'm doing wrong but all I get in my cells in F1-F25 are these: #N/A
I have tried using your code and revising the resize(25,1)

Sub Randoms()
Dim i As Long, j As Long, Arr
Randomize
For i = 1 To 5
For j = 1 To 5
Cells(i, j).Value = Int(Rnd * 100) + 1
Next j
Next i
Arr = Range("A1:E5")
Range("F1").Resize(25, 1).Value = Application.Transpose(Arr)
End Sub