Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decimal separators in formulas
I have a VBA application in Excel that puts formulas in various cells. For
example, I need cells that look like this: =IF(D4=0,0,D3/D4) This is pretty simple. But in some cases, I need to put an actual number from a variable. For example, I have this line of code: ...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" If nMyVar = 5, then I get this in cell A1: =IF(D4=0,0,5/D4) And if nMyVar = 5.5, I get this in cell A1: =IF(D4=0,0,5.5/D4) Again, this is pretty simple.. but for a little thing. Our application runs on PCs which have different regional settings for the decimal separator. So for those who use a comma (,) as a separator, the formula becomes: =IF(D4=0,0,5,5/D4) As you can see, the number of 'commas' in there will mess up the formula. When I use RecordMacro to do it manually, it will record it with a period (.) instead of a comma. But then if I program this, it won't work. Also, the RecordMacro will use semi-colon (; as the IF(;;) separator... (that's what it says in Excel's help file).. But again it won't work... My Excel will only accept commas for the IF (,,) separator. Any ideas anyone???? Thanks Somatophylax |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decimal separators in formulas
Hi
use: Cells(A1).formulaR1C1= "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" -- Regards Frank Kabel Frankfurt, Germany "Somatophylax" schrieb im Newsbeitrag ... I have a VBA application in Excel that puts formulas in various cells. For example, I need cells that look like this: =IF(D4=0,0,D3/D4) This is pretty simple. But in some cases, I need to put an actual number from a variable. For example, I have this line of code: ...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" If nMyVar = 5, then I get this in cell A1: =IF(D4=0,0,5/D4) And if nMyVar = 5.5, I get this in cell A1: =IF(D4=0,0,5.5/D4) Again, this is pretty simple.. but for a little thing. Our application runs on PCs which have different regional settings for the decimal separator. So for those who use a comma (,) as a separator, the formula becomes: =IF(D4=0,0,5,5/D4) As you can see, the number of 'commas' in there will mess up the formula. When I use RecordMacro to do it manually, it will record it with a period (.) instead of a comma. But then if I program this, it won't work. Also, the RecordMacro will use semi-colon (; as the IF(;;) separator... (that's what it says in Excel's help file).. But again it won't work... My Excel will only accept commas for the IF (,,) separator. Any ideas anyone???? Thanks Somatophylax |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decimal separators in formulas
Hi,
Try using the Formula property of the Rangeinstead of Value: Cells(A1).FormulaR1C1 = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" Similar properties: - Formula: A1 style, in the language of the marco - FormulaR1C1: R1C1 style, in the language of the macro - FormulaLocal: A1 style, in the language of the user - FormulaR1C1Local: R1C1 style , in the language of the user. Regards, Sebastien "Somatophylax" wrote: I have a VBA application in Excel that puts formulas in various cells. For example, I need cells that look like this: =IF(D4=0,0,D3/D4) This is pretty simple. But in some cases, I need to put an actual number from a variable. For example, I have this line of code: ...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" If nMyVar = 5, then I get this in cell A1: =IF(D4=0,0,5/D4) And if nMyVar = 5.5, I get this in cell A1: =IF(D4=0,0,5.5/D4) Again, this is pretty simple.. but for a little thing. Our application runs on PCs which have different regional settings for the decimal separator. So for those who use a comma (,) as a separator, the formula becomes: =IF(D4=0,0,5,5/D4) As you can see, the number of 'commas' in there will mess up the formula. When I use RecordMacro to do it manually, it will record it with a period (.) instead of a comma. But then if I program this, it won't work. Also, the RecordMacro will use semi-colon (; as the IF(;;) separator... (that's what it says in Excel's help file).. But again it won't work... My Excel will only accept commas for the IF (,,) separator. Any ideas anyone???? Thanks Somatophylax |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Decimal separators in formulas
Hi Somatophylax,
For example, I have this line of code: ...Cells(A1).Value = "=IF(RC[+3]=0,0," & nMyVar & "/RC[+3])" If nMyVar = 5, then I get this in cell A1: =IF(D4=0,0,5/D4) And if nMyVar = 5.5, I get this in cell A1: =IF(D4=0,0,5.5/D4) The problem is that when setting formulas for ranges, we always need to use the US-english function names and number formats (regardless of whether we're using .Value, .Formula or .FormulaR1C1). If we don't explicitly tell VBA how to convert a number to a string (as you're doing here), it will use the regional settings formats by default. Together, that means that whenever we build formula strings, we have to *explicitly* tell VBA to convert the number to a string using US number formats. We do that using the Str() function, so your code should be: ...Cells(A1).Value = "=IF(RC[+3]=0,0," & Str(nMyVar) & "/RC[+3])" There's much more about these types of issue in my 'International Issues' chapter of John Green's "Excel 2002 VBA Programmer's Reference", which can also be read online on my web site, at: http://www.oaltd.co.uk/ExcelProgRef/ch22/ Regards Stephen Bullen Microsoft MVP - Excel www.oaltd.co.uk |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Decimal Points and comma separators | Excel Discussion (Misc queries) | |||
Urgent - make formula work with both decimal separators | Excel Discussion (Misc queries) | |||
Can I insert a row that has no column separators? | Excel Discussion (Misc queries) | |||
I need separators on the X axis of a chart. | Charts and Charting in Excel | |||
how to remove separators from numbers | Excel Discussion (Misc queries) |