VBA: Type Mismatch error and cannot identify where

[willow1985]

I received help with this code on this forum but I had to make a bunch of modifications to it. Now I am getting a Type mismatch error and I have no idea where the error is. A lot of this code I am still fairly new at.

Any help identifying the error would be greatly appreciated!

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub RS()
'
' RS Macro
'
 
        If Application.CountIf(Sheets("Random Sample").Range("E1"), "") > 0 Then
        MsgBox "Please Enter User Name"
        Exit Sub
    End If
      If Application.CountIf(Sheets("Random Sample").Range("C1"), "") > 0 Then
        MsgBox "Please Enter Sample Size"
        Exit Sub
    End If
      If Application.CountIf(Sheets("Random Sample").Range("F1:I1"), "") > 0 Then
        MsgBox "Please Enter Sample Size"
        Exit Sub
    End If

Application.ScreenUpdating = False
    
 Sheets("Table").Visible = True
Sheets("DATA").Visible = True

Sheets("Table").Select
For Each lo In ActiveSheet.ListObjects
lo.AutoFilter.ShowAllData
      Next lo
      If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If

     Range("A3:E" & Cells(Rows.Count, 1).End(xlUp).Row).EntireRow.Delete
Range("A3").Select

Sheets("DATA").Range("A1:D1000000").ListObject.QueryTable.Refresh BackgroundQuery:=False
Sleep 2
DoEvents

    Range("A2:D" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    Selection.Copy

Sheets("Table").Select
    Range("A2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Calculate
    DoEvents
    Sheets("Table").Visible = False
Sheets("DATA").Visible = False
Sheets("Random Sample").Select
    
ActiveSheet.Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents
ActiveSheet.Range("B3:B" & Range("B3").End(xlDown).Row).ClearContents
 
   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)
 
Dim MyRange As Range
Dim LastRow As Long

LastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Set MyRange = ActiveSheet.Range("B3:B" & LastRow)

ActiveWorkbook.Worksheets("Random Sample").Sort.SortFields.Add2 Key:=Range( _
        "B3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Random Sample").Sort
        .SetRange MyRange
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

   Range("A3").Select
LastRowColumnB = Cells(Rows.Count, 2).End(xlUp).Row
    Range("A3:A" & LastRowColumnB).Formula = "=MATCH(RC[1],DATA!C,0)"

Application.ScreenUpdating = True


End Sub

 

[willow1985]

I figured it out. The code is fine and it was one sheet that was not selected.

Thank you to anyone who looked at this post

 

[NdNoviceHlp]

Hi Willow I just thought that I would mention using select and active sheet in your code will often result in problems as U encountered... it also slows down the execution of your code. Using selection is rarely ever needed for example...

Sheets("Random Sample").Select
 ActiveSheet.Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents

can be changed to specify exactly what sheet U want to do whatever with...

Sheets("Random Sample").Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents

HTH. Dave