willow1985
Well-known Member
- Joined
- Jul 24, 2019
- Messages
- 888
- Office Version
- 365
- Platform
- Windows
I have a code I have been using for awhile now that I had assistance writing, however now after an excel update to 365 the code no longer works and get the below error. I am unsure how to modify the code.
If anyone could help me with this it would be very much appreciated!
VBA Code:
Sub RS()
'
' RS Macro
'
If Application.CountIf(Sheets("Random Sample").Range("E1"), "") > 0 Then
MsgBox "Please Enter User Name"
Exit Sub
End If
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
sCellText = Sheets("Random Sample").Range("C1").Value
Dim R As Long, Cnt As Long, RandomIndex As Long, Arr As Variant, Tmp As Variant
' the below line is where the error is occuring
Const HowMany = sCellText
Randomize
Arr = Sheets("DATA").ListObjects("DATA").DataBodyRange.Value
With CreateObject("Scripting.Dictionary")
For R = 1 To UBound(Arr)
If Arr(R, 5) = "N" Then .Item(CStr(Arr(R, 1))) = 1
Next
Arr = .Keys
End With
For Cnt = UBound(Arr) To LBound(Arr) Step -1
RandomIndex = Int((Cnt - LBound(Arr) + 1) * Rnd + LBound(Arr))
Tmp = Arr(RandomIndex)
Arr(RandomIndex) = Arr(Cnt)
Arr(Cnt) = Tmp
Next
Sheets("Random Sample").Range("B3").Resize(HowMany) = Application.Transpose(Arr)
End Sub
If anyone could help me with this it would be very much appreciated!