Compile Error: Expected: End of Statement

Question

Compile Error: Expected: End of Statement

Anonymous

Here is a line of code that works in the cells, but not in VB:

Range("C8").Formula="=TRIM(MID([@AR],SEARCH ("48",[@AR])-2,12))"

It keeps coming back with an Expected: End of Statement error. The script works fine without it and I haven't been able to figure out what's causing the error. Any help would be greatly appreciated.

Also, if there is more efficient code to make this apply to cells C8, C10, C12, etc. I'd love to get some tips on that as well.

Thank you in advance for the assist!

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

1 answer

Answer 1

HansV 462.3K MVP Volunteer Moderator

You don't need quotes around 48.

Range("C8").Formula = "=TRIM(MID([@AR],SEARCH(48,[@AR])-2,12))"

By the way, if you are searching for a real text value, you need to double the quotes inside a quoted string, for example

Range("C8").Formula = "=TRIM(MID([@AR],SEARCH(""an"",[@AR])-2,12))"

Share via

Compile Error: Expected: End of Statement

1 answer