Decimal problem
In a VBA project I'm supporting, I ran into a line where a cell is assigned the value
"=RC[4] / (" + Format(100 - dblNewProdSaving) + " / 100)" I get a run-time error on this line. However, if I manually change the cell from where dblNewProdSaving is taken, from 0,02 to 0.02, it runs. Why is this and what can I do about it? Gustaf |
Decimal problem
syntax for format is wrong
"=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with format the result generally overcomes issues with results that can often unexpectedly occur when an interim calculation is formatted , eg rounding issue "Gustaf" wrote in message ... In a VBA project I'm supporting, I ran into a line where a cell is assigned the value "=RC[4] / (" + Format(100 - dblNewProdSaving) + " / 100)" I get a run-time error on this line. However, if I manually change the cell from where dblNewProdSaving is taken, from 0,02 to 0.02, it runs. Why is this and what can I do about it? Gustaf |
Decimal problem
syntax for format is wrong
"=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with format the result generally overcomes issues with results that can often unexpectedly occur when an interim calculation is formatted , eg rounding issue "Gustaf" wrote in message ... In a VBA project I'm supporting, I ran into a line where a cell is assigned the value "=RC[4] / (" + Format(100 - dblNewProdSaving) + " / 100)" I get a run-time error on this line. However, if I manually change the cell from where dblNewProdSaving is taken, from 0,02 to 0.02, it runs. Why is this and what can I do about it? Gustaf |
Decimal problem
Patrick Molloy wrote:
syntax for format is wrong "=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" That's what I thought too, but I get the same error. By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings? but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with Sorry to say, but this also produces the same error: "Application defined or object defined error" (duh!). Also tried putting NumberFormat first. As an alternative, I tried changing the format of the cell from which I take dblNewProdSaving: dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%") But this results in a "type mismatch" error. Ideally, I'd like to change it behind the scenes, to avoid confusing the user. Gustaf |
Decimal problem
Patrick Molloy wrote:
syntax for format is wrong "=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" That's what I thought too, but I get the same error. By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings? but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with Sorry to say, but this also produces the same error: "Application defined or object defined error" (duh!). Also tried putting NumberFormat first. As an alternative, I tried changing the format of the cell from which I take dblNewProdSaving: dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%") But this results in a "type mismatch" error. Ideally, I'd like to change it behind the scenes, to avoid confusing the user. Gustaf |
Decimal problem
what's the value in dblNewProdSaving when you step through?
"Gustaf" wrote in message ... Patrick Molloy wrote: syntax for format is wrong "=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" That's what I thought too, but I get the same error. By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings? but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with Sorry to say, but this also produces the same error: "Application defined or object defined error" (duh!). Also tried putting NumberFormat first. As an alternative, I tried changing the format of the cell from which I take dblNewProdSaving: dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%") But this results in a "type mismatch" error. Ideally, I'd like to change it behind the scenes, to avoid confusing the user. Gustaf |
Decimal problem
what's the value in dblNewProdSaving when you step through?
"Gustaf" wrote in message ... Patrick Molloy wrote: syntax for format is wrong "=RC[4] / (" + Format(100 - dblNewProdSaving ,"0.00" ) + " / 100)" That's what I thought too, but I get the same error. By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings? but i suggest with rangetarget .formulaR1C1 = "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" .NumberFormat ="0.00%" end with Sorry to say, but this also produces the same error: "Application defined or object defined error" (duh!). Also tried putting NumberFormat first. As an alternative, I tried changing the format of the cell from which I take dblNewProdSaving: dblNewProdSaving = Format(ws.Cells(5, 4), "0.00%") But this results in a "type mismatch" error. Ideally, I'd like to change it behind the scenes, to avoid confusing the user. Gustaf |
Decimal problem
Patrick Molloy wrote:
what's the value in dblNewProdSaving when you step through? The value is 0,02 in the cell and still 0,02 when transfered to dblNewProdSaving. If I change the cell to 0.02 the value of dblNewProdSaving becomes 2 when I step through. Looks odd but probably makes sense, because the variable is a percentage. Gustaf |
Decimal problem
Patrick Molloy wrote:
what's the value in dblNewProdSaving when you step through? The value is 0,02 in the cell and still 0,02 when transfered to dblNewProdSaving. If I change the cell to 0.02 the value of dblNewProdSaving becomes 2 when I step through. Looks odd but probably makes sense, because the variable is a percentage. Gustaf |
Decimal problem
that makes a pretty big difference!
so "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" should be "=RC[4] / (" & (1 - dblNewProdSaving ) & ")" "Gustaf" wrote in message ... Patrick Molloy wrote: what's the value in dblNewProdSaving when you step through? The value is 0,02 in the cell and still 0,02 when transfered to dblNewProdSaving. If I change the cell to 0.02 the value of dblNewProdSaving becomes 2 when I step through. Looks odd but probably makes sense, because the variable is a percentage. Gustaf |
Decimal problem
that makes a pretty big difference!
so "=RC[4] / (" & (100 - dblNewProdSaving ) & ")" should be "=RC[4] / (" & (1 - dblNewProdSaving ) & ")" "Gustaf" wrote in message ... Patrick Molloy wrote: what's the value in dblNewProdSaving when you step through? The value is 0,02 in the cell and still 0,02 when transfered to dblNewProdSaving. If I change the cell to 0.02 the value of dblNewProdSaving becomes 2 when I step through. Looks odd but probably makes sense, because the variable is a percentage. Gustaf |
Decimal problem
"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"
Gustaf No. It's governed by regional settings. John |
Decimal problem
"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"
Gustaf No. It's governed by regional settings. John |
Decimal problem
"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"
Gustaf No. It's governed by regional settings. But the regional settings can be overridden in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" wrote: "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. John |
Decimal problem
"By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?"
Gustaf No. It's governed by regional settings. But the regional settings can be overridden in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" wrote: "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. John |
Decimal problem
Hi Chip,
Yes, I should have pointed that out also. Toolsoptionsinternational "system separators" John "Chip Pearson" wrote in message ... "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. But the regional settings can be overridden in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" wrote: "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. John |
Decimal problem
Hi Chip,
Yes, I should have pointed that out also. Toolsoptionsinternational "system separators" John "Chip Pearson" wrote in message ... "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. But the regional settings can be overridden in Excel. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Wed, 3 Jun 2009 12:09:39 -0400, "jaf" wrote: "By the way, am I right that VBA always expects "." as decimal sign, regardless of my regional settings?" Gustaf No. It's governed by regional settings. John |
All times are GMT +1. The time now is 09:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com