Need help inserting formula with VBA

[VBA FTW]

I have a formula I'm trying to insert with VBA, but I keep getting a compile error. The formula is looking for blank spaces between words, and the VB editor keeps saying there is a compile error and highlights the area of my formula that has " ". Here is the formula I'm trying to insert with VBA:

=IF(A2="","",IF((LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=1),A2,(LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)-1))))

When I put this in the cell manually, it works fine. But I cannot insert it using VBA. Here's my VBA line:

Please Login or Register  to view this content.

Here's where I'm getting hit with an error:

Please Login or Register  to view this content.

It says End of Statement Expected.

I've tried recording a macro while entering this formula, but that doesn't work either. Here's what the recorder gave me:

Please Login or Register  to view this content.

As you can see, it's R1C1 format. When I tried using the above in my code, it did put the formulas in the cells, however, it replaced column A with column BDF...which is far from correct. Any suggestions?

[Norie]

Try this.

Please Login or Register  to view this content.

By the way, you could get that by doing a find and replace on the R1C1 formula, finding R[-1]C[-2] and replacing with A2, oh and you'd have to change to Formula.

[VBA FTW]

Wow, Norie. You're great! Really, you are always so helpful and are a wealth of knowledge. I appreciate it. This is solved!