Insert LEFT function into IF formula

I'm finishing up some work on a macro which basically pulls data from one big data-sheet and puts it in the correct order in another.

Essentially what I want it to do is insert the IF-statement formula into cells A2:A40, which then should pull the first 4 characters from column AP starting with row 2 in worksheet 'SomeSheet', under the condition that cell A2:A40 is not blank.

Now most of what i needed the macro to do was pretty easy for me to piece together, but I'm kind of new at doing formulas in VBA and I've been struggling for the last 2 hours trying to get this one to work:

    Range("A2:A40").Formula = "=IF(A2="""","""","LEFT("'SomeSheet'!$AP2", "4")")"

It's a fairly simple line of "code" but I get syntax error in the "LEFT" function and I can't seem to wrap my head around what's causing the issue.