Run Time Error 13 - Type Mismatch

[VBABeginner1]

Hello,
I have been pulling my hair out trying to troubleshoot this code. The code was originally created with recorded marco's. I had it working when it referenced a single employee and tracked the output from a userform into their employee file, but since I have to write the code for multiple employees I have attempted to introduce arrays to make it easier to code later down the road as employee turnover happens.

I am receiveing a "Run Time Error 13 - Type Mismatch" on the line below that is in RED.

Please help as I have spent too much time on this code and can't move any farther.

Dim EmpDrop1, EmpDrop2, EmpDrop3 As String
Dim AttendCell As String

Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("ShiftReport")
EmpDrop1 = ws.Range("g1").End(xlDown).Value
EmpDrop2 = ws.Range("h1").End(xlDown).Value
EmpDrop3 = ws.Range("i1").End(xlDown).Value
AttendCell = ws.Range("d1").End(xlDown).Value
Dim AttendArray As Variant, y As Long
Dim EmpArray As Variant, X As Long
EmpArray = Application.Transpose(Range("EmployeeList"))
For X = LBound(EmpArray) To UBound(EmpArray) '<-- This loops from the lower boundary to the upper boundary

Next
AttendArray = Application.Transpose(Range("AttendanceType"))
For y = LBound(AttendArray) To UBound(AttendArray)
Next
If AttendCell = AttendArray And EmpDrop1 = EmpArray Then

'EmpFileAttend Macro
Sheets("ShiftReport").Range("A1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("G3").End(xlDown).Range("A1").Paste
Sheets("ShiftReport").Range("C1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("H3").End(xlDown).Offset(1, 0).Range("A1").Paste 
Sheets("ShiftReport").Range("D1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("I3").End(xlDown).Offset(1, 0).Range("A1").Paste
Sheets("ShiftReport").Range("L1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("J3").End(xlDown).Offset(1, 0).Range("A1").Paste
Sheets("ShiftReport").Range("F1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("K3").End(xlDown).Offset(1, 0).Range("A1").Paste

'' EmpFileAttend Macro
    ActiveCell.Offset(0, -4).Range("A1:E1").Select
    ActiveCell.Activate
    Application.CutCopyMode = False
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
        Call boxthem
        
    End With
ElseIf AttendCell = AttendArray And EmpDrop1 = EmpArray Or EmpDrop2 = EmpArray Or EmpDrop3 = EmpArray Then
    Range("A1").End(xlDown).Range("A1:F1").Copy
    Sheets(EmpArray).Range("A1").End(xlDown).Offset(1, 0).Range("A1").Paste
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    Call boxthem
    End With
End If

 

[ZabutonNage]

AttendCell is declared as a String while AttendArray is a Variant and becomes what is returned from Application.Transpose(...), which is an array of Variants, i.e. Variant().
String and Variant() are not comparable. But you can compare the string with a single element from that array which is probably what you are intending to do.

If AttendCell = AttendArray(y) Then

The same applies to EmpDrop1 and EmpArray.

Also, check your For-Loops. At the moment they aren't doing anything.

EmpArray = Application.Transpose(Range("EmployeeList"))
For X = LBound(EmpArray) To UBound(EmpArray) '<-- This loops from the lower boundary to the upper boundary[INDENT]' Nothing done with X[/INDENT]
Next
AttendArray = Application.Transpose(Range("AttendanceType"))
For y = LBound(AttendArray) To UBound(AttendArray)[INDENT]' Nothing done with y either[/INDENT]
Next
If AttendCell = AttendArray And EmpDrop1 = EmpArray Then

I believe you meant to create a nested loop. Here is what you are probably looking for.

For X = LBound(EmpArray) To UBound(EmpArray)[INDENT]For y = LBound(AttendArray) To UBound(AttendArray)[/INDENT]
[INDENT=2]If AttendCell = AttendArray(y) And EmpDrop1 = EmpArray(X) Then
[/INDENT]
[INDENT=3]' Do whatever shall be done when the condition is met.[/INDENT]
[INDENT=2]End If
[/INDENT]
[INDENT]Next y
[/INDENT]
Next X

By the way, EmpDrop1 and EmpDrop2 are declared as Variants. Only EmpDrop3 is a string. I don't know whether this is intended but if not, you need to append As String to each variable name in order to make them strings.

 

[VBABeginner1]

Thanks for your help. I have now passed through that error into another (URGH!!) It has now taken me longer to build this small section of code then to create the rest of the program. I am now getting a Runtime error 438: Object doesn't support this property or method. I think it has something to do with where the next x & next y end up.

Dim EmpDrop1 As String, EmpDrop2 As String, EmpDrop3 As String
Dim AttendCell As String
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("ShiftReport")
EmpDrop1 = ws.Range("g1").End(xlDown).Value
EmpDrop2 = ws.Range("h1").End(xlDown).Value
EmpDrop3 = ws.Range("i1").End(xlDown).Value
AttendCell = ws.Range("d1").End(xlDown).Value
Dim AttendArray As Variant, y As Long
Dim EmpArray As Variant, X As Long
EmpArray = Application.Transpose(Range("EmployeeList"))
AttendArray = Application.Transpose(Range("AttendanceType"))
For X = LBound(EmpArray) To UBound(EmpArray) '<-- This loops from the lower boundary to the upper boundary
For y = LBound(AttendArray) To UBound(AttendArray)
    'Do Something Here using EmployeeArray(X) to get the current loops value in the array
 

If AttendCell = AttendArray(y) And EmpDrop1 = EmpArray(X) Then

'EmpFileAttend Macro
Sheets("ShiftReport").Range("A1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("G3").End(xlDown).Range("A1").Paste
Sheets("ShiftReport").Range("C1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("H3").End(xlDown).Offset(1, 0).Range("A1").Paste '<-- You can probably remove the Range("A1"). from each of these lines
Sheets("ShiftReport").Range("D1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("I3").End(xlDown).Offset(1, 0).Range("A1").Paste
Sheets("ShiftReport").Range("L1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("J3").End(xlDown).Offset(1, 0).Range("A1").Paste
Sheets("ShiftReport").Range("F1").End(xlDown).Copy
Sheets(EmpArray(X)).Range("K3").End(xlDown).Offset(1, 0).Range("A1").Paste

'' EmpFileAttend Macro
    ActiveCell.Offset(0, -4).Range("A1:E1").Select
    ActiveCell.Activate
    Application.CutCopyMode = False
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
           Call boxthem
    End With

ElseIf AttendCell = AttendArray(y) = False And EmpDrop1 = EmpArray(X) Or EmpDrop2 = EmpArray(X) Or EmpDrop3 = EmpArray(X) Then
    Range("A1").End(xlDown).Range("A1:F1").Copy
    Sheets(EmpArray(X)).Range("A1").End(xlDown).Offset(1, 0).Range("A1").Paste
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    Call boxthem
    End With
End If
      Next y
Next X

 

[ZabutonNage]

Took me a moment to reproduce that error but I think I finally found it.
Any of the Sheets(EmpArray(X)).Range("G3").End(xlDown).Range("A1").Paste commands should cause it. Here, .Paste is invoked on a range object. However Range doesn't have a member .Paste, therefore "Object doesn't support this method." Check the Object Browser (F2 key) to see what is available.

The easiest way to fix this should be to change .Paste to .PasteSpecial. You can provide some optional parameters as you need.
You also have the option to invoke .Paste on the worksheet object and provide the target range as a parameter.
This could look something like this:

Dim ws As Worksheet
Set ws = Sheets(EmpArray(X))
ws.Paste ws.Range("G3").End(xlDown).Range("A1")

Hope this solves your problem.