| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to add commas to the result of a formula with a text operator? | Excel Discussion (Misc queries) | |||
| Formula to Add $ and/or commas | Excel Worksheet Functions | |||
| Help! I need a formula to add numbers separated by commas within | Excel Worksheet Functions | |||
| formula to count commas | Excel Discussion (Misc queries) | |||
| Autoselection of Spreadsheet for liking | Excel Discussion (Misc queries) | |||
Hybrid Mode
