VBA: Type Mismatch error and cannot identify where

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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 :)
 
Upvote 0
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...
Code:
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...
Code:
Sheets("Random Sample").Range("A3:A" & Range("A3").End(xlDown).Row).ClearContents
HTH. Dave
 
Upvote 0
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top