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



## yuro0804 (Monday at 2:13 PM)

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 (Monday at 2:19 PM)

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


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

Hope this helps!


----------



## yuro0804 (Monday at 3:26 PM)

Domenic said:


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


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


----------



## JEC (Monday at 3:58 PM)

Try


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


----------



## Domenic (Monday at 4:37 PM)

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!


----------



## yuro0804 (Monday at 5:10 PM)

Domenic said:


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


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


----------

