Compile error: Constant expression required for Random Sample code

[willow1985]

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.

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!

 

[Fluff]

Try

Dim HowMany As Long
HowMany = sCellText

 

[willow1985]

Got it:

Sheets("Random Sample").Select
Dim R As Long, Cnt As Long, RandomIndex As Long, HowMany As Long, Arr As Variant, Tmp As Variant
  HowMany = Sheets("Random Sample").Range("C1").Value
  Randomize
  Arr = Sheets("Table").ListObjects("WO").DataBodyRange.Value
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Arr)
      If Arr(R, 5) = "Y" 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)

Thank you guys for your help

 

[Fluff]

Glad we could help & thanks for the feedback.