.Formula Runtime error 1004 on some machines and not the other
I'll go straight to the point. Here is the line code that is geving me the error:
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
The above code works on my home machine (where created) and does not work on my lab or work machine
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
And the second one works on my lab and work machine, but does not in my home machine.
The only difference is Formula and FormulaLocal. I know FormulaLocal could give me errors because of different installations of excel might have different language.... but shouldn't Formula work everywhere?
Also, if I change cells to R16C9 instead of I16 it works in my home machine and bugs in others. I really want a code that can work everywhere here. Can someone help? Thanks
P.S.: Variables are all ok and well defined, but the issue is probably that they have decimals, thus the comma or dot issue
Debug.Print (With FormulaLocal) gives me:
=-(I16)+0,549840740436919+(0,0513418101762096*((604,958825198337 ^ 1,04086742681856)*(I16 ^ 1,04086742681856)/(49,44 ^ 1,04086742681856)))
excel vba excel-vba excel-formula
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
add a comment |
I'll go straight to the point. Here is the line code that is geving me the error:
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
The above code works on my home machine (where created) and does not work on my lab or work machine
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
And the second one works on my lab and work machine, but does not in my home machine.
The only difference is Formula and FormulaLocal. I know FormulaLocal could give me errors because of different installations of excel might have different language.... but shouldn't Formula work everywhere?
Also, if I change cells to R16C9 instead of I16 it works in my home machine and bugs in others. I really want a code that can work everywhere here. Can someone help? Thanks
P.S.: Variables are all ok and well defined, but the issue is probably that they have decimals, thus the comma or dot issue
Debug.Print (With FormulaLocal) gives me:
=-(I16)+0,549840740436919+(0,0513418101762096*((604,958825198337 ^ 1,04086742681856)*(I16 ^ 1,04086742681856)/(49,44 ^ 1,04086742681856)))
excel vba excel-vba excel-formula
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
2
Difficult to know what the problem might be without seeing the missing pieces -Debug.Printthe formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error?Formulashould work if the string you're passing is a valid formula in a version of Excel with English as the language setting.
– Tim Williams
Nov 15 '18 at 5:09
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48
add a comment |
I'll go straight to the point. Here is the line code that is geving me the error:
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
The above code works on my home machine (where created) and does not work on my lab or work machine
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
And the second one works on my lab and work machine, but does not in my home machine.
The only difference is Formula and FormulaLocal. I know FormulaLocal could give me errors because of different installations of excel might have different language.... but shouldn't Formula work everywhere?
Also, if I change cells to R16C9 instead of I16 it works in my home machine and bugs in others. I really want a code that can work everywhere here. Can someone help? Thanks
P.S.: Variables are all ok and well defined, but the issue is probably that they have decimals, thus the comma or dot issue
Debug.Print (With FormulaLocal) gives me:
=-(I16)+0,549840740436919+(0,0513418101762096*((604,958825198337 ^ 1,04086742681856)*(I16 ^ 1,04086742681856)/(49,44 ^ 1,04086742681856)))
excel vba excel-vba excel-formula
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
I'll go straight to the point. Here is the line code that is geving me the error:
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
The above code works on my home machine (where created) and does not work on my lab or work machine
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
And the second one works on my lab and work machine, but does not in my home machine.
The only difference is Formula and FormulaLocal. I know FormulaLocal could give me errors because of different installations of excel might have different language.... but shouldn't Formula work everywhere?
Also, if I change cells to R16C9 instead of I16 it works in my home machine and bugs in others. I really want a code that can work everywhere here. Can someone help? Thanks
P.S.: Variables are all ok and well defined, but the issue is probably that they have decimals, thus the comma or dot issue
Debug.Print (With FormulaLocal) gives me:
=-(I16)+0,549840740436919+(0,0513418101762096*((604,958825198337 ^ 1,04086742681856)*(I16 ^ 1,04086742681856)/(49,44 ^ 1,04086742681856)))
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
edited Nov 15 '18 at 12:48
Marco Faria
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
asked Nov 15 '18 at 3:26
Marco FariaMarco Faria
62
62
2
Difficult to know what the problem might be without seeing the missing pieces -Debug.Printthe formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error?Formulashould work if the string you're passing is a valid formula in a version of Excel with English as the language setting.
– Tim Williams
Nov 15 '18 at 5:09
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48
add a comment |
2
Difficult to know what the problem might be without seeing the missing pieces -Debug.Printthe formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error?Formulashould work if the string you're passing is a valid formula in a version of Excel with English as the language setting.
– Tim Williams
Nov 15 '18 at 5:09
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48
2
2
Difficult to know what the problem might be without seeing the missing pieces -
Debug.Print the formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error? Formula should work if the string you're passing is a valid formula in a version of Excel with English as the language setting.– Tim Williams
Nov 15 '18 at 5:09
Difficult to know what the problem might be without seeing the missing pieces -
Debug.Print the formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error? Formula should work if the string you're passing is a valid formula in a version of Excel with English as the language setting.– Tim Williams
Nov 15 '18 at 5:09
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48
add a comment |
1 Answer
1
active
oldest
votes
There are probably better solutions, mainly on the dumb way i used that if, but this now works on every machine i have. Thanks everyone.
...
bloqueio = 5
On Error GoTo alternativa
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
If bloqueio = 0 Then
alternativa:
On Error GoTo -1
On Error GoTo Erro
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
End If
On Error GoTo -1
...
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53311974%2fformula-runtime-error-1004-on-some-machines-and-not-the-other%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are probably better solutions, mainly on the dumb way i used that if, but this now works on every machine i have. Thanks everyone.
...
bloqueio = 5
On Error GoTo alternativa
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
If bloqueio = 0 Then
alternativa:
On Error GoTo -1
On Error GoTo Erro
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
End If
On Error GoTo -1
...
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
add a comment |
There are probably better solutions, mainly on the dumb way i used that if, but this now works on every machine i have. Thanks everyone.
...
bloqueio = 5
On Error GoTo alternativa
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
If bloqueio = 0 Then
alternativa:
On Error GoTo -1
On Error GoTo Erro
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
End If
On Error GoTo -1
...
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
add a comment |
There are probably better solutions, mainly on the dumb way i used that if, but this now works on every machine i have. Thanks everyone.
...
bloqueio = 5
On Error GoTo alternativa
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
If bloqueio = 0 Then
alternativa:
On Error GoTo -1
On Error GoTo Erro
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
End If
On Error GoTo -1
...
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
There are probably better solutions, mainly on the dumb way i used that if, but this now works on every machine i have. Thanks everyone.
...
bloqueio = 5
On Error GoTo alternativa
ThisWorkbook.Worksheets("Dados").Cells(16, 10).Formula = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
If bloqueio = 0 Then
alternativa:
On Error GoTo -1
On Error GoTo Erro
ThisWorkbook.Worksheets("Dados").Cells(16, 10).FormulaLocal = "=-(I16)+" & a & "+(" & c & "*((" & PiZi & " ^ " & d & ")*(I16 ^ " & d & ")/(" & Pc & " ^ " & d & ")))"
End If
On Error GoTo -1
...
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
answered Nov 15 '18 at 16:23
Marco FariaMarco Faria
62
62
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53311974%2fformula-runtime-error-1004-on-some-machines-and-not-the-other%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Difficult to know what the problem might be without seeing the missing pieces -
Debug.Printthe formula and add the output to the question. Copy the output and try adding it to the cell via the formula bar - do you get an error? If Yes, what error?Formulashould work if the string you're passing is a valid formula in a version of Excel with English as the language setting.– Tim Williams
Nov 15 '18 at 5:09
As already mentioned the first thing to do is Debug.Print and inspect the formula. As is this means nothing. You have five variables going into that string whose value is unknown. It's quite possible the variable values are the issue
– Nick.McDermaid
Nov 15 '18 at 7:46
Completely forgot to mention that yes, variables are the issue because they have decimals.
– Marco Faria
Nov 15 '18 at 11:48