Excel VBA Modifying Ranges and Selecting Random Cells

Question

I have a cell range that I am selecting one single cell from, removing the cell from the range, and then selecting another random cell. This loop is performed a total of 6 times.

Upon the completion of each loop both Range.Count and Range.Address reflect the removal, but while the loop is running, it will continue to potentially select removed cells. I use the cell removal function elsewhere and it performs as expected.

If both the count and the addresses for the range are showing the removal, why are those removed cells being selected? What am I doing wrong?

Code is as follows:

someRange = RangeToString(namesList)
    MsgBox someRange

    someDate = namesSheet.Range("F12").Value
    For i = 1 To 3
        For j = 1 To 2
            For k = 1 To namesList.Count
                Set randomName = GetRandomName(namesList)
                MsgBox randomName & " was selected for date " & someDate & " and the size of the range is: " & namesList.Count
                available = AvailabilityCheck(randomName, someDate )
                If available = True Then
                    If i = 1 And j = 1 Then
                        namesSheet.Cells(18, "E").Value = randomName
                    ElseIf i = 1 And j = 2 Then
                        namesSheet.Cells(19, "E").Value = randomName
                    ElseIf i = 2 And j = 1 Then
                        namesSheet.Cells(18, "I").Value = randomName
                    ElseIf i = 2 And j = 2 Then
                        namesSheet.Cells(19, "I").Value = randomName
                    ElseIf i = 3 And j = 1 Then
                        namesSheet.Cells(18, "K").Value = randomName
                    ElseIf i = 3 And j = 2 Then
                        namesSheet.Cells(19, "K").Value = randomName
                    End If

                    Set namesList = ExcludeCell(namesList, genSheet.Range(randomName.Address))
                    namesString = RangeToString(namesList)
                    MsgBox namesList.Address
                Exit For
                End If
            Next k
        Next j
        If i = 1 Then
            someDate = namesSheet.Range("J12").Value
        Else
            someDate = namesSheet.Range("L12").Value
        End If
    Next i
    Set FooFunction = namesList

And the function to remove the cells is:

Function ExcludeCell(ByVal rngMain As Range, rngExc As Range) As Range

    Dim rngTemp     As Range
    Dim RNG         As Range

    Set rngTemp = rngMain
    Set rngMain = Nothing

    For Each RNG In rngTemp
        If RNG.Address <> rngExc.Address Then
            If rngMain Is Nothing Then
                Set rngMain = RNG
            Else
                Set rngMain = Union(rngMain, RNG)
            End If
        End If
    Next
    Set ExcludeCell = rngMain
End Function

Jeremy H. Jeremy H. · Accepted Answer · 2018-05-29T20:54:43

This had to do not with the exclusion function but the random cell generator that I was using. Since I am not dealing with a contiguous range, I needed to account for that in my random generator code.

Here is the before:

Function GetRandomName(namesList As Range) As Range

    Dim randomName As Long
    randomName = Int(Rnd * namesList.Cells.Count) + 1

    Set GetRandomName = namesList.Cells(randomName)

End Function

Here is the after:

Function GetRandomName(namesList As Range) As Range

    Dim randomCell As Integer
    Dim randomName As Range

    Do
        randomCell = Int(Rnd * namesList.Cells.Count) + 1
        Set randomName = namesList.Cells(randomCell)

        If (randomName.Parent.name = namesList.Parent.name) Then
            Dim ints As Range

            For Each cell In namesList
                Set ints = Application.Intersect(cell, randomName)
                If (Not (ints Is Nothing)) Then
                    Set GetRandomName = randomName
                    Exit Do
                End If
            Next cell
        End If
    Loop
End Function

Excel VBA Modifying Ranges and Selecting Random Cells

1 Answers