formula error, vba not liking commas

I am trying to put the following formula into a piece of code (using the
variable 'sFormula' to hold the formula) and get the error message as below,
any ideas how i can get around this. I know i could place it on the
worksheet somewhere and copy it but would like to keep it in code if possible

sFormula="=IF(LEFT(RIGHT(A7,2),1)=",","."&RIGHT(A7,1),"")"

"Compile error expected end of statement"

it appears not to like the first comma in inverted commas

Any ideas will be gratefully received
--
with kind regards

Spike

Within string literals, double quotes mus be doubled:
sFormula="=IF(LEFT(RIGHT(A7,2),1)="","","".""&RIGHT(A7,1),"""""")"

HTH
--
AP
"Spike" <Spike@discussions.microsoft.com> a écrit dans le message de
news:AC42E07D-4948-4805-ADFB-EEB65D560F03@microsoft.com...
>
> I am trying to put the following formula into a piece of code (using the
> variable 'sFormula' to hold the formula) and get the error message as
below,
> any ideas how i can get around this. I know i could place it on the
> worksheet somewhere and copy it but would like to keep it in code if
possible
>
> sFormula="=IF(LEFT(RIGHT(A7,2),1)=",","."&RIGHT(A7,1),"")"
>
> "Compile error expected end of statement"
>
> it appears not to like the first comma in inverted commas
>
> Any ideas will be gratefully received
> --
> with kind regards
>
> Spike

sFormula = "=IF(LEFT(RIGHT(A7,2),1)="","","".""&RIGHT(A7,1),"""")"

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Spike" <Spike@discussions.microsoft.com> wrote in message
news:AC42E07D-4948-4805-ADFB-EEB65D560F03@microsoft.com...
>
> I am trying to put the following formula into a piece of code (using the
> variable 'sFormula' to hold the formula) and get the error message as
below,
> any ideas how i can get around this. I know i could place it on the
> worksheet somewhere and copy it but would like to keep it in code if
possible
>
> sFormula="=IF(LEFT(RIGHT(A7,2),1)=",","."&RIGHT(A7,1),"")"
>
> "Compile error expected end of statement"
>
> it appears not to like the first comma in inverted commas
>
> Any ideas will be gratefully received
> --
> with kind regards
>
> Spike

Thank you both of you, much appreciated

You learn something new every day!!
--
with kind regards

Spike


"Spike" wrote:

>
> I am trying to put the following formula into a piece of code (using the
> variable 'sFormula' to hold the formula) and get the error message as below,
> any ideas how i can get around this. I know i could place it on the
> worksheet somewhere and copy it but would like to keep it in code if possible
>
> sFormula="=IF(LEFT(RIGHT(A7,2),1)=",","."&RIGHT(A7,1),"")"
>
> "Compile error expected end of statement"
>
> it appears not to like the first comma in inverted commas
>
> Any ideas will be gratefully received
> --
> with kind regards
>
> Spike

If you don't double up on the quotes, it thinks the first one is the end of
the string and then gets confused by what follows <g>

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Spike" <Spike@discussions.microsoft.com> wrote in message
news:6218B109-6C41-400F-8AB9-EA5B208E147F@microsoft.com...
> Thank you both of you, much appreciated
>
> You learn something new every day!!
> --
> with kind regards
>
> Spike
>
>
> "Spike" wrote:
>
> >
> > I am trying to put the following formula into a piece of code (using the
> > variable 'sFormula' to hold the formula) and get the error message as
below,
> > any ideas how i can get around this. I know i could place it on the
> > worksheet somewhere and copy it but would like to keep it in code if
possible
> >
> > sFormula="=IF(LEFT(RIGHT(A7,2),1)=",","."&RIGHT(A7,1),"")"
> >
> > "Compile error expected end of statement"
> >
> > it appears not to like the first comma in inverted commas
> >
> > Any ideas will be gratefully received
> > --
> > with kind regards
> >
> > Spike