Type Mismatch error when trying to insert index match formula using vba?

Question

I am trying to insert the following index match formula with wildcards using vba like so:

Cells(i, 13) = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(" * " & Range(""C"" & i).Value & " * ",Contacts!$B:$B,0)),"""")"

For some reason i get a type mismatch error. Please can someone show me what i'm doing wrong?

EDIT:

Cells(i, 13).Formula = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(""*"" & """ & Range("G" & i).value & """ & ""*"",Contacts!$B:$B,0)),IFERROR(INDEX(Contacts!$C:$C,MATCH(""*"" & LEFT(""" & Range("G" & i).value & ,7) """ & ""*"",Contacts!$B:$B,0)),""""))"

Answer 1

Just like you put four double quotes to get the two, you need to put two to get one:

Cells(i, 13).Formula = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(""*"" & """ & Range("C" & i).Value & """ & ""*"",Contacts!$B:$B,0)),"""")"

Answer 2

Use

Cells(i, 13).Formula = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(""*" & Range("C" & i).Value & "*"",Contacts!$B:$B,0)),"""")"

Answer 3

You need to specify that it is a formula with .Formula :

Cells(i, 13) = "=IFERROR(INDEX(Contacts!$C:$C,MATCH(""*" & Range("C" & i).Value & "*"",Contacts!$B:$B,0)),"""")"

Furthermore, your range (""C"") wasn't properly recognized with your code

Answer 4

With abc in C5 of the activesheet,

Dim i As Long
i = 5
With ActiveSheet
    .Cells(i, 13).Formula = "=IFERROR(INDEX(Contacts!$C:$C,MATCH("" * " & .Cells(i, "C").Value & " * "",Contacts!$B:$B,0)),"""")"
End With

This produces the formula,

=IFERROR(INDEX(Contacts!$C:$C,MATCH(" * abc * ",Contacts!$B:$B,0)),"")

... in M5. I'm not entirely clear on whether you want the wrapping spaces or not.