Runtime Error 13 Type mismatch

[manos123]

Hey guys! This is my first time programming something in vba so pls don't be to harsh.

Every time I run my code I get the error "runtime error 13: Type mismatch", but I don't know why.
Everything except the block where I try to get rid of the 0s and NAs works perfectly fine.

It says the error appears in "WorksheetFunction.IsNA(Worksheets("CEO").Cells(i, j + 1))".

Sub CEOUpdate()
'
' CEOUpdate Makro
'

'
    Sheets("CEO").Select
    Sheets("CEO").Copy Before:=ActiveSheet
    ActiveSheet.Name = "CEO Temp"
    Sheets("CEO").Select
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
    
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    
    
    Dim i As Long
    Dim j As Long
    
    Sheets("CEO").Range(Cells(5, 15), Cells(lastrow, 42)).NumberFormat = "General"
    
    For i = 5 To lastrow
        For j = 14 To 41
            Worksheets("CEO").Cells(i, j + 1).Formula = _
                "=VLOOKUP(R" & i & "C2,'CEO Temp'!R5C2:R" & lastrow & "C42," & j & ",0)"
        Next j
    Next i
    
    For i = 5 To lastrow
        For j = 14 To 41
            If WorksheetFunction.IsNA(Worksheets("CEO").Cells(i, j + 1)) Then
                Worksheets("CEO").Cells(i, j + 1).Value.Clear
            ElseIf Worksheets("CEO").Cells(i, j + 1) = "0" Then
                Worksheets("CEO").Cells(i, j + 1).Clear
            End If
        Next j
    Next i
    
    Dim l As Integer
    For l = 0 To 3
        Sheets("CEO").Range(Cells(5, 17 + l * 7), Cells(lastrow, 17 + l * 7)).NumberFormat = "dd.mm.yyyy"
    Next l
    
    Range(Cells(5, 15), Cells(lastrow, 42)).Select
    Selection.Copy
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
    Sheets("CEO Temp").Select
    ActiveWindow.SelectedSheets.Delete
    
End Sub

Thanks for your help!
BR

 

[DanteAmor]

Hi and welcome to MrExcel!

I simplified your macro a bit. I used another approach to replace the NA error and the zeros.

Try this:

Sub CEOUpdate()
  Dim sh As Worksheet
  Dim i As Long, lr As Long

  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Set sh = Sheets("CEO")
  sh.Copy Before:=sh
  ActiveSheet.Name = "CEO Temp"
  sh.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
  lr = sh.Range("A" & Rows.Count).End(xlUp).Row
    
  With sh.Range(sh.Cells(5, 15), sh.Cells(lr, 42))
    .NumberFormat = "General"
    .Formula = "=IFERROR(VLOOKUP($B5,'CEO Temp'!$B$5:$AP$" & lr & ",column() -1,0),"""")"
    .Value = .Value
    .Replace 0, "", xlWhole
  End With
  
  sh.Range("Q5:Q" & lr & ",X5:X" & lr & ",AE5:AE" & lr & ",AL5:AL1" & lr).NumberFormat = "dd.mm.yyyy"
  Sheets("CEO Temp").Delete
  
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
End Sub

 

[manos123]

Your code worked! Thank you so much!!

FYI: I had to replace "sh.Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False" with the following lines of code:
sh.Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
But now it works perfectly fine! Thanks

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.