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

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

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

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

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

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

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

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

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

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

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

"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

"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

"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

"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

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

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