|
Issue regarding Regional settings
Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use , instead of . for decimals and . instead of , for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = =if( & num & =0,0,5) and I got an error message. I also used this formula: Cells(1,1).formula = =if( & num & =0;0;5) Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
Issue regarding Regional settings
While this doesn't answer your regional setting question, why would you want to put the *value* in B1 in your formula's comparison... wouldn't you want B1 itself to go there? Putting the regional setting aside, I would think your formula assignment should be this... Cells(1, 1).Formula = =IF(B1=0,0,5) or, if your arguments to Cells(1,2) are really variables, then this... Cells(1, 1).Formula = "=IF(" & Cells(R, C).Address & "=0,0,5) where you can use the optional arguments of the Address property to set the relative/absolute row/column references. -- Rick (MVP - Excel) "T.Mad" wrote in message ... Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use , instead of . for decimals and . instead of , for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = =if( & num & =0,0,5) and I got an error message. I also used this formula: Cells(1,1).formula = =if( & num & =0;0;5) Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
Issue regarding Regional settings
in an English language version (excel 2007 with win Xp)
the following works without any error Num = Cells(1, 2).Value Cells(1, 1).Formula = "=If(" & Num & "=0,0,5)" Please press "YES" if this post is helpful Thanks, Abdul "T.Mad" wrote: Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use , instead of . for decimals and . instead of , for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = =if( & num & =0,0,5) and I got an error message. I also used this formula: Cells(1,1).formula = =if( & num & =0;0;5) Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
Issue regarding Regional settings
Hi, Thanks for the answer, because this was just a simple example. Huge amount of data is collected, through out the spreadsheet and many intermediate calculations are taking place. I wonder if there is a vba function converting the value of a variable from the local to the international format? "Rick Rothstein" wrote: While this doesn't answer your regional setting question, why would you want to put the *value* in B1 in your formula's comparison... wouldn't you want B1 itself to go there? Putting the regional setting aside, I would think your formula assignment should be this... Cells(1, 1).Formula = =IF(B1=0,0,5) or, if your arguments to Cells(1,2) are really variables, then this... Cells(1, 1).Formula = "=IF(" & Cells(R, C).Address & "=0,0,5) where you can use the optional arguments of the Address property to set the relative/absolute row/column references. -- Rick (MVP - Excel) "T.Mad" wrote in message ... Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use , instead of . for decimals and . instead of , for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = =if( & num & =0,0,5) and I got an error message. I also used this formula: Cells(1,1).formula = =if( & num & =0;0;5) Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
Issue regarding Regional settings
You are going to have to wait for someone else who is knowledgeable about such things for that question... I have never had to deal with international issues in any of my programming throughout the years. -- Rick (MVP - Excel) "T.Mad" wrote in message ... Hi, Thanks for the answer, because this was just a simple example. Huge amount of data is collected, through out the spreadsheet and many intermediate calculations are taking place. I wonder if there is a vba function converting the value of a variable from the local to the international format? "Rick Rothstein" wrote: While this doesn't answer your regional setting question, why would you want to put the *value* in B1 in your formula's comparison... wouldn't you want B1 itself to go there? Putting the regional setting aside, I would think your formula assignment should be this... Cells(1, 1).Formula = =IF(B1=0,0,5) or, if your arguments to Cells(1,2) are really variables, then this... Cells(1, 1).Formula = "=IF(" & Cells(R, C).Address & "=0,0,5) where you can use the optional arguments of the Address property to set the relative/absolute row/column references. -- Rick (MVP - Excel) "T.Mad" wrote in message ... Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use , instead of . for decimals and . instead of , for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = =if( & num & =0,0,5) and I got an error message. I also used this formula: Cells(1,1).formula = =if( & num & =0;0;5) Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
Issue regarding Regional settings
I don't know if there is anything useful for your specific problem, but this site gives a lot of info on regional settings. http://www.microsoft.com/resources/d...spx?mfr= true I would think that once the setting is change to Greece, that you should be able to use all local symbols and characters without creating error messages. But, I don't know that for sure. "T.Mad" wrote in message ... Hi, Thanks for the answer, because this was just a simple example. Huge amount of data is collected, through out the spreadsheet and many intermediate calculations are taking place. I wonder if there is a vba function converting the value of a variable from the local to the international format? "Rick Rothstein" wrote: While this doesn't answer your regional setting question, why would you want to put the *value* in B1 in your formula's comparison... wouldn't you want B1 itself to go there? Putting the regional setting aside, I would think your formula assignment should be this... Cells(1, 1).Formula = "=IF(B1=0,0,5)" or, if your arguments to Cells(1,2) are really variables, then this... Cells(1, 1).Formula = "=IF(" & Cells(R, C).Address & "=0,0,5) where you can use the optional arguments of the Address property to set the relative/absolute row/column references. -- Rick (MVP - Excel) "T.Mad" wrote in message ... Hi there, I am programming in VBA having set up the Greek Regional settings. This means that in Greece we use "," instead of "." for decimals and "." instead of "," for thousands. I had to pass an excel formula via VBA which is: Dim num as Double Num=Cells(1,2).value ' this number is 10.1 (10,1 with Greek symbols) Cells(1,1).formula = "=if(" & num & "=0,0,5)" and I got an error message. I also used this formula: Cells(1,1).formula = "=if(" & num & "=0;0;5)" Which is the exact way to input the IF function (;) to Excel with Greek regional settings but again I got this error. Can anybody give me a good piece of advice? Many Thanks |
problem solved
You just have to use the string function, str( variable ).
Thus: Dim num as Double Num=Cells(1,2).value Cells(1,1).formula = =if( & str(num) & =0,0,5) |
| All times are GMT +1. The time now is 08:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com