VBA Collections value - Run time error 9 - subscript out of range

[Legacy 502047]

Hi everyone,

I'm new to collections and I keep getting a "subscript out of range" error on the underlined bit of code when I'm trying to set the value of the cell, wondering where I'm going wrong here?

Sub GetRandomEmptyCell()
Dim i, j, randomcell As Integer
Dim allcells As New Collection

For i = 1 to 10
For j = 1 to 4
If Cells(i, j) = "" Then
allcells.Add Cells(i, j)
End If
Next j
Next i

If allcells.Count >= 1 Then
randomcell = Rnd * allcells.Count
allcells(randomcell).Value = 2
allcells.Remove randomcell
End If
End Sub

Thanks for your help
yuro0804

 

[Domenic]

The indexing for a collection starts at 1, not 0. Therefore, try the following instead...

randomcell = (Rnd * allcells.Count) + 1

Hope this helps!

 

[Legacy 502047]

The indexing for a collection starts at 1, not 0. Therefore, try the following instead...

randomcell = (Rnd * allcells.Count) + 1

Hope this helps!

Hi Domenic, thanks for your reply, unfortunately it hasn't fixed the error but still good to know, thanks!

 

[JEC]

Try

If allcells.Count >= 1 Then
   randomcell = Rnd * allcells.Count
   allcells(randomcell) = 2
   Debug.Print allcells(randomcell)
   allcells.Remove randomcell
 End If

 

[Domenic]

I missed it earlier, but you need to use the Int function on the result before assigning it to your variable...

randomcell = Int(Rnd * allcells.Count) + 1

The Int function removes the fractional part of the number and returns the resulting integer. Whereas assigning the result to an integer variable rounds the number to the nearest integer.

Hope this helps!

 

[Legacy 502047]

I missed it earlier, but you need to use the Int function on the result before assigning it to your variable...

randomcell = Int(Rnd * allcells.Count) + 1

The Int function removes the fractional part of the number and returns the resulting integer. Whereas assigning the result to an integer variable rounds the number to the nearest integer.

Hope this helps!

Ah that makes sense, perfect, seems to have fixed it! Thanks so much