.Formula Find Compile error

[MrBuzz]

I am trying to have a macro put a formula into Cell J10 that contains the Excel Find function and I get get an "expected end of statement" compile error. The formula works if I manually enter it into a cell on the spreadsheet.

Cell F10 contains +123.45 USDC I want to parse out the number which can vary in length. While writing the macro VBA highlights the U and says expected end of statement. I saw the post about adding spaces which I tried with no improvement. I thought VBA would just look at the formula within the parentheses as a string of text so I am confused as to why it is trying to compile the code as a command.

RANGE("J10") . formula = "=IF(F10<>"",VALUE(LEFT(F10,FIND( "U" , F10 )-1)),"")"

I get the same error with this simpler code: RANGE("J10").Formula = "=FIND("U",F10)"

Any help appreciated. Thanks for your time.

 

[Michael M]

When inserting formulae into a sheet using VBA, ALL quotes need to be double quotes

Range("J10").Formula = "=IF(F10<>"""",VALUE(LEFT(F10,FIND( ""U"" , F10 )-1)),"""")"

 

[MrBuzz]

When inserting formulae into a sheet using VBA, ALL quotes need to be double quotes

Range("J10").Formula = "=IF(F10<>"""",VALUE(LEFT(F10,FIND( ""U"" , F10 )-1)),"""")"

Thanks Michael. I had tried doubling some of the quotes but not all of them. I downloaded the XL2BB file and will read it in the next couple of days. Cheers