CODEWITHSUNDEEP
excelvba
Muhnamana
Muhnamana

Reputation: 1044

Selecting A Random Cell From Another Worksheet

I'm trying to select a random cell from another worksheet. The following code works if I select a random cell on the current sheet where the code is at. How can I select a random cell from another sheet?

Dim e
Static myList As Object
If myList Is Nothing Then
    Set myList = CreateObject("System.Collections.SortedList")
End If
If myList.Count = 0 Then
    Randomize
    For Each e In Range("D3:D32", Range("D" & Rows.Count).End(xlUp)).Value
        myList.Item(Rnd) = e
    Next
End If
MsgBox myList.GetByIndex(0)
myList.RemoveAt 0

Here's what I tried to select from another sheet.

Dim e
Static myList As Object
If myList Is Nothing Then
    Set myList = CreateObject("System.Collections.SortedList")
End If
If myList.Count = 0 Then
    Randomize
    For Each e In Workbooks("Test").Sheets("Sheet1").Range("D3:D32", Range("D" & Rows.Count).End(xlUp)).Value
        myList.Item(Rnd) = e
    Next
End If
MsgBox myList.GetByIndex(0)
myList.RemoveAt 0

Suggestions on what I'm doing wrong?

Upvotes: 0

Views: 897

Answers (1)

Joe
Joe

Reputation: 6827

In this line:

For Each e In Workbooks("Test").Sheets("Sheet1").Range("D3:D32", Range("D" & Rows.Count).End(xlUp)).Value

you're specifying the "Test" workbook for the first call to Range, but not for the second call, nor for Rows.Count (though this will probably be the same). As a quick fix:

For Each e In Workbooks("Book2").Sheets("Sheet1").Range("D3:D32", Workbooks("Book2").Sheets("sheet1").Range("D" & Workbooks("Book2").Sheets("sheet1").Rows.Count).End(xlUp)).Value

or:

With Workbooks("Book2").Sheets("Sheet1")
    For Each e In .Range("D3:D32", .Range("D" & .Sheets("sheet1").Rows.Count).End(xlUp)).Value

Upvotes: 2

Related Questions