Formula, VBA, Run-time error 1004 - Application-defined or object-defined error

Hi gurus,

I seem unable to make the following code working. I have been scratching my head with no luck. Anyone to help please

Run-time error 1004 - Application-defined or object-defined error or error 400.

Sub test()

Dim lastRow As Long

lastRow = Sheets("CHG").Range("B" & Rows.Count).End(xlUp).Row

Range("F4:F" & lastRow).Formula = "=IF(ISNUMBER(SEARCH(""Maco"",$E4)),MID($E4,87,31),IF(ISNUMBER(SEARCH(""David"",$E4)),MID($E4,88,31),IF(ISNUMBER(SEARCH(""Alberto"",$E4)),MID($E4,89,32),IF(ISNUMBER(SEARCH(""Khaled"",$E4)),MID($E4,89,31),IF(ISNUMBER(SEARCH(""Cargo"",$E4)),MID($E4,109,31),IF(ISNUMBER(SEARCH(""Maggie"",$E4)),MID($E4,88,32),""TBC"")))))"

End Sub