error '1004' "application defined or object defined error" when pasting formula with macro

When I run the following macro

Dim w As Integer
    For w = 6 To 39
        Range("C" & w).Formula = "=IF(LEN($B" & w & ")>=1,"" ""&VLOOKUP(LEFT($B" & w & "), '[Ch Name index.xls]Family Names'!$A$1:$B$13017,2,0), "")&IF(LEN($B" & w & ")>=2,"" ""&VLOOKUP(MID($B" & w & ",2,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0), "")&IF(LEN($B" & w & ")>=3,"" ""&VLOOKUP(MID($B" & w & ",3,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0),"")&IF(LEN($B" & w & ")=4,"" ""&VLOOKUP(MID($B" & w & ",4,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0),"")"
    Next w
            Range("D6:G39").Select
            Selection.ClearContents
            Range("D6:G39").FormulaR1C1 = ChrW(&H221A)
    Dim x As Integer
    For x = 6 To 39
        Range("P" & x).Formula = "=IF(ISERROR($C" & x & "),"",LOOKUP(COUNTIF(D" & x & ":G" & x & "," & A & ")*1+SUM(D" & x & ":G" & x & "),$BC$8:$BC$16,$BD$8:$BD$16))"
    Next x
    For x = 6 To 39
        Range("W" & x).Formula = "=LOOKUP(COUNTIF(D" & x & ":G" & x & "," & A & ")*1+SUM(D" & x & ":G" & x & "),$BC$8:$BC$16,$BE$8:$BE$16)"
    Next x
    For x = 6 To 39
        Range("AK" & x).Formula = "=LOOKUP(COUNTIF(D" & x & ":G" & x & "," & A & ")*1+SUM(D" & x & ":G" & x & "),$BC$8:$BC$16,$BF$8:$BF$16)"
    Next x
    For x = 6 To 39
        Range("AL" & x).Formula = "=LOOKUP(COUNTIF(D" & x & ":G" & x & "," & A & ")*1+SUM(D" & x & ":G" & x & "),$BC$8:$BC$16,$BG$8:$BG$16)"
    Next x
    Range("K9").Select

It hangs up at this line

Range("C" & w).Formula = "=IF(LEN($B" & w & ")>=1,"" ""&VLOOKUP(LEFT($B" & w & "), '[Ch Name index.xls]Family Names'!$A$1:$B$13017,2,0), "")&IF(LEN($B" & w & ")>=2,"" ""&VLOOKUP(MID($B" & w & ",2,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0), "")&IF(LEN($B" & w & ")>=3,"" ""&VLOOKUP(MID($B" & w & ",3,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0),"")&IF(LEN($B" & w & ")=4,"" ""&VLOOKUP(MID($B" & w & ",4,1), '[Ch Name index.xls]all Characters'!$A$1:$B$15041,2,0),"")"

and tells me "error '1004' application defined or object defined error"

What do I need to change?