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

formula error, vba not liking commas
 

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

HTH
--
AP
"Spike" a écrit dans le message de
...

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

formula error, vba not liking commas
 

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Spike" wrote in message
...

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

formula error, vba not liking commas
 

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

formula error, vba not liking commas
 

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" wrote in message
...
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