VBA “Application-defined or object-defined error”
Despite many hits, I have been unable to find an answer for my issue. I wrote a small routine to insert a formula after the last column of some data in a sheet.
My issue is that I am adding a formula and getting
"Application-defined or object-defined error".
I take the formula directly from the worksheet where it returns a result and try to add it to a copy of the sheet. I have done this successfully with other formulas. Without going in to the routine, the line that gives the error is:
Range(Cells(headerRow + 1, lastCol).Address, Cells(lastRow, lastCol).Address).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-29],'[dataFile.xlsx]Report Page_1'!$A$2:$B$1475,2,FALSE), ""-"")"
It works if I remove the equal sign so that it is only adding text to the specified range. So the issue appears to be with the formula I am adding, but it does work when the formula is pasted in to the top cell and filled down the column. In the final version in the VBA it will use R1C1 format for relative references.
Can anyone help me with this problem?
excel vba excel-vba
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
asked Nov 20 at 20:00
Ben
196
|
show 3 more comments
Despite many hits, I have been unable to find an answer for my issue. I wrote a small routine to insert a formula after the last column of some data in a sheet.
My issue is that I am adding a formula and getting
"Application-defined or object-defined error".
I take the formula directly from the worksheet where it returns a result and try to add it to a copy of the sheet. I have done this successfully with other formulas. Without going in to the routine, the line that gives the error is:
Range(Cells(headerRow + 1, lastCol).Address, Cells(lastRow, lastCol).Address).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-29],'[dataFile.xlsx]Report Page_1'!$A$2:$B$1475,2,FALSE), ""-"")"
It works if I remove the equal sign so that it is only adding text to the specified range. So the issue appears to be with the formula I am adding, but it does work when the formula is pasted in to the top cell and filled down the column. In the final version in the VBA it will use R1C1 format for relative references.
Can anyone help me with this problem?
excel vba excel-vba
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
asked Nov 20 at 20:00
Ben
196
1
Why are you usingFormulaR1C1and then usingA2as cell reference?
– Luuklag
Nov 20 at 20:03
...use.Formulainstead
– Tim Williams
Nov 20 at 20:06
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
2
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before allRange()objects (includingCells()), e.g.Worksheets("Sheet100").Range("A1"). 2) You don't need the.Addressafter theCells()
– BruceWayne
Nov 20 at 21:04
|
show 3 more comments
Despite many hits, I have been unable to find an answer for my issue. I wrote a small routine to insert a formula after the last column of some data in a sheet.
My issue is that I am adding a formula and getting
"Application-defined or object-defined error".
I take the formula directly from the worksheet where it returns a result and try to add it to a copy of the sheet. I have done this successfully with other formulas. Without going in to the routine, the line that gives the error is:
Range(Cells(headerRow + 1, lastCol).Address, Cells(lastRow, lastCol).Address).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-29],'[dataFile.xlsx]Report Page_1'!$A$2:$B$1475,2,FALSE), ""-"")"
It works if I remove the equal sign so that it is only adding text to the specified range. So the issue appears to be with the formula I am adding, but it does work when the formula is pasted in to the top cell and filled down the column. In the final version in the VBA it will use R1C1 format for relative references.
Can anyone help me with this problem?
excel vba excel-vba
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
asked Nov 20 at 20:00
Ben
196
Despite many hits, I have been unable to find an answer for my issue. I wrote a small routine to insert a formula after the last column of some data in a sheet.
My issue is that I am adding a formula and getting
"Application-defined or object-defined error".
I take the formula directly from the worksheet where it returns a result and try to add it to a copy of the sheet. I have done this successfully with other formulas. Without going in to the routine, the line that gives the error is:
Range(Cells(headerRow + 1, lastCol).Address, Cells(lastRow, lastCol).Address).FormulaR1C1 = "=IFNA(VLOOKUP(RC[-29],'[dataFile.xlsx]Report Page_1'!$A$2:$B$1475,2,FALSE), ""-"")"
It works if I remove the equal sign so that it is only adding text to the specified range. So the issue appears to be with the formula I am adding, but it does work when the formula is pasted in to the top cell and filled down the column. In the final version in the VBA it will use R1C1 format for relative references.
Can anyone help me with this problem?
excel vba excel-vba
excel vba excel-vba
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
asked Nov 20 at 20:00
Ben
196
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
asked Nov 20 at 20:00
Ben
196
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
edited Nov 21 at 7:37
Pᴇʜ
20.1k42650
20.1k42650
asked Nov 20 at 20:00
Ben
196
asked Nov 20 at 20:00
Ben
196
asked Nov 20 at 20:00
Ben
196
196
1
Why are you usingFormulaR1C1and then usingA2as cell reference?
– Luuklag
Nov 20 at 20:03
...use.Formulainstead
– Tim Williams
Nov 20 at 20:06
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
2
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before allRange()objects (includingCells()), e.g.Worksheets("Sheet100").Range("A1"). 2) You don't need the.Addressafter theCells()
– BruceWayne
Nov 20 at 21:04
|
show 3 more comments
1
Why are you usingFormulaR1C1and then usingA2as cell reference?
– Luuklag
Nov 20 at 20:03
...use.Formulainstead
– Tim Williams
Nov 20 at 20:06
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
2
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before allRange()objects (includingCells()), e.g.Worksheets("Sheet100").Range("A1"). 2) You don't need the.Addressafter theCells()
– BruceWayne
Nov 20 at 21:04
1
1
Why are you using
FormulaR1C1 and then using A2 as cell reference?– Luuklag
Nov 20 at 20:03
Why are you using
FormulaR1C1 and then using A2 as cell reference?– Luuklag
Nov 20 at 20:03
...use
.Formula instead– Tim Williams
Nov 20 at 20:06
...use
.Formula instead– Tim Williams
Nov 20 at 20:06
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
2
2
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before all
Range() objects (including Cells()), e.g. Worksheets("Sheet100").Range("A1"). 2) You don't need the .Address after the Cells()– BruceWayne
Nov 20 at 21:04
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before all
Range() objects (including Cells()), e.g. Worksheets("Sheet100").Range("A1"). 2) You don't need the .Address after the Cells()– BruceWayne
Nov 20 at 21:04
|
show 3 more comments
active
oldest
votes
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%2f53400667%2fvba-application-defined-or-object-defined-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53400667%2fvba-application-defined-or-object-defined-error%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
1
Why are you using
FormulaR1C1and then usingA2as cell reference?– Luuklag
Nov 20 at 20:03
...use
.Formulainstead– Tim Williams
Nov 20 at 20:06
I updated it to RC[-29] as I am actually using R1C1 format. I had pasted that part in to a separate test sub to get it working and had put "A2" there but it should have said RC[-29]. The error is still there though.
– Ben
Nov 20 at 20:15
OK. I think I see the issue - will be checking now but I am not using R1C1 format for the absolute references in the data file. Thanks Luuklag. I am guessing that this will fix the issue.
– Ben
Nov 20 at 20:23
2
A couple points: 1) If you have more than one worksheet in this workbook, please add the worksheet reference before all
Range()objects (includingCells()), e.g.Worksheets("Sheet100").Range("A1"). 2) You don't need the.Addressafter theCells()– BruceWayne
Nov 20 at 21:04