From an indexed list I need to select 3 unique names randomly. The index number is generated by a cell within the spreadsheet (Cell A1: =RANDBETWEEN(1,26) ); in actual application the 26 is not static.
The macro searches for the index number and records both it and the name to which it is indexed. The Selection and ????? columns are there more for my verification except that, if the line: Range("G" & 4 + c).Value = Range("A1").Value is commented, after a time of repeatedly pressing the button the macro freezes and Ctrl/Break produces a 91 error.
That is my first issue.
My second is: If my data does not begin in the second row (or my search does not begin from cell B1) then index 1 is seemingly never selected. Why?
I am confident that the macro will always generate three names successfully, but I need some assurance that my propositions about my two issues are correct.
That said, if there is a simple way, consistent with my given macro, that overcomes both issues then Id be willing to be advised. Please.
Triple.jpg
Here is my macro:
Sub triple()
Range("E1").Select
Dim str As String
For c = 1 To 3
Do
n = 0
str = Range("A1").Value
Range("G" & 4 + c).Value = Range("A1").Value 'Why this line?
'Find str
Range("B1:B27").Find(What:=str, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate
Do Until ActiveCell.Value <> ActiveCell.Offset(1, 0).Value
Cells.FindNext(After:=ActiveCell).Activate
Loop
For m = 1 To c - 1
If ActiveCell.Value = Range("D" & 4 + m) Then
n = n + 1
End If
Next
Loop While n > 0
ActiveCell.Resize(1, 2).Copy
Range("D" & 4 + c).Select
ActiveSheet.Paste
Range("F" & 4 + c).Value = c
Next
End Sub
Bookmarks