type mismatch VBA checking cell value

Question

I am trying to understand why I get Type mismatch error:

This is the function I have, basically it is copying from a worksheet to another and afterwards deleting the first character of the copied cells:

Sub copyBackFormulas()

Application.ScreenUpdating = False
Application.EnableEvents = False

'iterate through all worksheets
Dim WS_Count As Integer
WS_Count = ActiveWorkbook.Worksheets.Count
Dim I As Integer


For I = 1 To WS_Count
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets(I)
    'if sheet contains evdre
    Set d = ws1.Cells.Find("EVDRE:OK")
    If Not d Is Nothing Then
        'copy back all formulas except from current view
        Dim wsTarget As Worksheet
        Set wsTarget = ws1
        nameHidden = ActiveSheet.Name & "_BPCOffline"
        Sheets(nameHidden).Visible = True
        Dim wsSource As Worksheet
        Set wsSource = Sheets(nameHidden)

        For Each c In wsSource.UsedRange.Cells
            If Left(c.Value, 1) = "_" Then
                    If Left(c.Value, 7) = "_=EVCVW" Then
                    Else
                        c.Copy wsTarget.Range(c.Address)
                    End If
            End If
        Next
        'Remove underscore
        For Each c In wsTarget.UsedRange.Cells
            If Left(c.Value, 1) = "_" Then
                c.Formula = Right(c.Value, Len(c.Value) - 1)
            End If
        Next
        wsSource.Visible = xlSheetHidden
    End If
    Range("A1").Select
Next I

Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

I have several sheets that may need to be copied. The point is that I get type mismatch error on the line: If Left(c.Value, 1) = "_" Then However, if I run the macro starting from other sheet it just works perfectly or it is only doing the right operations on one of the sheets and not the others. I don't understand what makes it work at some point and what not. Any input is highly appreciated

EDIT: I think the issue has to do with the fact that the macro may not find the first condition If Left(c.Value, 1) = "_" Then

What is in the cells that is being evaluated, how are those cells formatted? If they are formatted as numbers it of course can't find "_" — Luuklag
Also instead of using IF... THEN ELSE, you could use: IF NOT ... THEN — Luuklag
the content of the cells loks like this: _=EVCVW($G$2,F3) and they are formatted as general; as I said it works sometimes and sometimes not and all the worksheets are formatted the same — user3540466
@user1582568 Neither of those is actually true. You would get a Type Mismatch if the cell contains an error value though. — Rory
Correct. If you want to skip cells with errors you need another If...End if block: If Not Iserror(c.Value) Then — Rory

user3540466 user3540466 · Accepted Answer · 2015-12-30T16:35:13

You CAN'T copy paste formulas which have an error value

If you want to skip cells with errors you need another If...End if block:

If Not Iserror(c.Value) Then
...
End if

As explained by Rory in the comments

type mismatch VBA checking cell value

1 Answers