I have a statement that looks like this:

Wks.range("A1") = "=IF(A,0,(IF(B,0,0))"

This statment works on several user machines, but not on one remote
user. Assume following:
(1) VBA had created a new empty sheet prior to this statement
(2) Imperial conventions ( comma delimited)
(3) Assume that strings have previously been inserted into the sheet so
that protection is not an issue

Any guesses on why this would not work on some machines, and not
others?

I've ask the user to insert .formula after the range to see if that
helps. I have several other elementary questions:
(1) If the default property for a range is value, why does the
statement work at all?
(2) If Excel or VBA is smart enough to work out that property needs to
be converted to a formula, then why is .formula needed at all for an
assignment?

As you can tell, I'm still learning the ropes.
Thanks in advance

BTW,
The error message he is getting:

Runtime-error '1004'
App-defined or object-defined error

The user has a 8 hour time zone difference from me, so I'm trying to
understand in advance of his modification to
Wks.range("A1").formulaR1C1 = "=IF(A,0,(IF(B,0,0))"

1. The formula has 3 left parentheses and 2 right. It shouldn't work at all.
Correction: "=IF(A,0,(IF(B,0,0)))" or "=If(A, 0, If(B, 0, 0))"

2. I don't understant the formula. Are A and B substitues for condition
statements of some kind that you have left out of the post? Also, the
formula as written will always return 0.

Regards,
Greg


"ken4capitola" wrote:

BTW,
The error message he is getting:

Runtime-error '1004'
App-defined or object-defined error

The user has a 8 hour time zone difference from me, so I'm trying to
understand in advance of his modification to
Wks.range("A1").formulaR1C1 = "=IF(A,0,(IF(B,0,0))"

To answer your other questions, and a caution that I'm no expert, but I have
always assumed that when you (via VBA) tell Excel to make the VALUE of a cell
a text string that happens to begin with an equals sign then it is really no
different then any other text string as far as VBA is concerned. However,
Excel subsequently interprets it as a formula because of the leading equals
sign. This is no different in principal then when you enter it manually: type
in either "Bob" or "=B1 + C1" and it's the same operation (typing text) but
Excel then goes and interprets the second statement as a formula.

AFAIK, the need for the "Formula" property in VBA is really only necessary
for read as opposed to write operations. Example:

Cell A1 has the formual "=A1 + B1" in it. If you query the VALUE property of
the cell then it will return the result of the formula, i.e. 22 if B1 = 10
and C1 = 12. However, if you query the Formual property then it will return
"=A1 + B1".

Regards,
Greg

Thanks Greg,
Sorry for the mistyping of the equation. In this simplistic example, A
and B are names of cells containing values. My suggestion to the user
didn't correct the problem as I think your responses are indicating.

The actual code is shown below. The replace statement is to allow the
code to work on both sides of the Atlantic( "," vs ";" problem). The
statement does work with both listseparators as verified by others. As
above, the names you see are range names containing values that should
be ok.

Any idea how someone could have a Excel setting that would prevent the
assignment not to work.

Dim ListSep As String
Dim BackComma As String
BackComma = "\,"
ListSep = Application.International(xlListSeparator)
...
WKS.Range("AD17") = Replace("=IF(iVO2Min<wVO2MinLow \, wVO2MinLow \,
IF(iVO2Min<wVO2MinHigh \, wVO2MinHigh \, iVO2Min))", BackComma,
ListSep)

Next step is that I've ask to the Austria user to manually enter the
code into the spreadsheet when the VBA error breaks. He will enter
= IF(iVO2Min<wVO2MinLow ; wVO2MinLow ; IF(iVO2Min<wVO2MinHigh ;
wVO2MinHigh ; iVO2Min))

I'm hoping his manually entering will fail because of some Excel
setting. But this is getting out of my league.
Any other ideas?

I never use the Replace worksheet function nor deal with the international
list separator issue. I have on occasion used the VBA Replace function which
is significantly different. (You can achieve much the same as the VBA version
through EditReplace I believe). Also, a reminder that I'm no expert.

My version of the Excel's Replace worksheet function (xl2000 sp3) has 5
required arguments: old_text, start_num, num_chars and new_text. Your post
shows only 3. This seems appropriate only for the VBA Replace function and I
suggest this may be the source of the problem. I couldn't glean from your
posts if this interpretation is appropriate.

Unfortunately, the Replace worksheet function as I understand it doesn't
appear to support what you're trying to do. It apparently will only allow you
to replace a single block of text within the original text as opposed to
multiple and separate instances of text (in your case a single character)
within the original text. It's late and I'm off to bed so I can't give this
more than a cursory evaluation. I suggest you confirm/deny this and if
confirmed try another approach.

Sorry for the typo in my post. I'm sure you got the point anyway. Should
have been:

Cell A1 has the formual "=A1 + B1" in it. If you query the VALUE property of
the cell then it will return the result of the formula, i.e. 22 if A1 = 10
and B1 = 12. However, if you query the Formual property then it will return
"=A1 + B1".

Best regards,
Greg

"ken4capitola" wrote:

Thanks Greg,
Sorry for the mistyping of the equation. In this simplistic example, A
and B are names of cells containing values. My suggestion to the user
didn't correct the problem as I think your responses are indicating.

The actual code is shown below. The replace statement is to allow the
code to work on both sides of the Atlantic( "," vs ";" problem). The
statement does work with both listseparators as verified by others. As
above, the names you see are range names containing values that should
be ok.

Any idea how someone could have a Excel setting that would prevent the
assignment not to work.

Dim ListSep As String
Dim BackComma As String
BackComma = "\,"
ListSep = Application.International(xlListSeparator)
...
WKS.Range("AD17") = Replace("=IF(iVO2Min<wVO2MinLow \, wVO2MinLow \,
IF(iVO2Min<wVO2MinHigh \, wVO2MinHigh \, iVO2Min))", BackComma,
ListSep)

Next step is that I've ask to the Austria user to manually enter the
code into the spreadsheet when the VBA error breaks. He will enter
= IF(iVO2Min<wVO2MinLow ; wVO2MinLow ; IF(iVO2Min<wVO2MinHigh ;
wVO2MinHigh ; iVO2Min))

I'm hoping his manually entering will fail because of some Excel
setting. But this is getting out of my league.
Any other ideas?

Sorry Ken, I gave it a little too cursory a read. You WERE using the VBA
Replace function and I somehow got the worksheet function version in my head.
As I said, it's late for me. Sorry I couldn't have had more to offer.

Greg

"Greg Wilson" wrote:

I never use the Replace worksheet function nor deal with the international
list separator issue. I have on occasion used the VBA Replace function which
is significantly different. (You can achieve much the same as the VBA version
through EditReplace I believe). Also, a reminder that I'm no expert.

My version of the Excel's Replace worksheet function (xl2000 sp3) has 5
required arguments: old_text, start_num, num_chars and new_text. Your post
shows only 3. This seems appropriate only for the VBA Replace function and I
suggest this may be the source of the problem. I couldn't glean from your
posts if this interpretation is appropriate.

Unfortunately, the Replace worksheet function as I understand it doesn't
appear to support what you're trying to do. It apparently will only allow you
to replace a single block of text within the original text as opposed to
multiple and separate instances of text (in your case a single character)
within the original text. It's late and I'm off to bed so I can't give this
more than a cursory evaluation. I suggest you confirm/deny this and if
confirmed try another approach.

Sorry for the typo in my post. I'm sure you got the point anyway. Should
have been:

Cell A1 has the formual "=A1 + B1" in it. If you query the VALUE property of
the cell then it will return the result of the formula, i.e. 22 if A1 = 10
and B1 = 12. However, if you query the Formual property then it will return
"=A1 + B1".

Best regards,
Greg

"ken4capitola" wrote:

Thanks Greg,
Sorry for the mistyping of the equation. In this simplistic example, A
and B are names of cells containing values. My suggestion to the user
didn't correct the problem as I think your responses are indicating.

The actual code is shown below. The replace statement is to allow the
code to work on both sides of the Atlantic( "," vs ";" problem). The
statement does work with both listseparators as verified by others. As
above, the names you see are range names containing values that should
be ok.

Any idea how someone could have a Excel setting that would prevent the
assignment not to work.

Dim ListSep As String
Dim BackComma As String
BackComma = "\,"
ListSep = Application.International(xlListSeparator)
...
WKS.Range("AD17") = Replace("=IF(iVO2Min<wVO2MinLow \, wVO2MinLow \,
IF(iVO2Min<wVO2MinHigh \, wVO2MinHigh \, iVO2Min))", BackComma,
ListSep)

Next step is that I've ask to the Austria user to manually enter the
code into the spreadsheet when the VBA error breaks. He will enter
= IF(iVO2Min<wVO2MinLow ; wVO2MinLow ; IF(iVO2Min<wVO2MinHigh ;
wVO2MinHigh ; iVO2Min))

I'm hoping his manually entering will fail because of some Excel
setting. But this is getting out of my league.
Any other ideas?