VBA Formula Application/Object Defined Error
![Multi tool use Multi tool use](http://sgv.ssvwv.com/sg/ssvwvcomimagb.png)
Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I know there's a bazillion of these questions out there; but I've tried implementing things from various ones I've seen and haven't hit upon the right structure yet. I keep getting application/object defined errors. I'm sure it's the quotation marks but I can't seem to figure out where I have too many vs not enough. The end goal is for this formula to appear after a certain range of rows (newrows variable) and update dynamically as it fills down each row after that.
I've tried removing the extra quotes around the string sections, but it then gives me an "Expected: end of statement" error near the beginning of the code. When I'm viewing it in Notepad ++ it looks correct, but still get the errors in VBA itself when running the macro in excel.
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(""E""" & newrows & "= ""Planning"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,3,FALSE),IF(""E""" & newrows & "=""Fieldwork"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,4,FALSE),IF(""E""" & newrows & "=""Reporting"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,5,FALSE),IF(""E""" & newrows & "=""Wrap Up"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),IF(""E""" & newrows & "=""Proj. Mgmt"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),"""")))))"
The code is supposed to be looking up a value based on the value of a cell in a row (Column E) and the name of a project (Column D). The newrows is a numerical variable being generated by a dynamic list. The top portion of the sheet has existing tasks I don't want to overwrite but I want this formula in all cells after the end row of that list. I've been learning VBA as I've been working on this project so it may be something I just haven't come across yet so I appreciate the help!
ETA: This morning I tried turning the sequence with newrows into a variable with the column letter instead and got the same application defined error.
firstloc = "E" & newrows
secondloc = "D" & newrows
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
excel vba
add a comment |
I know there's a bazillion of these questions out there; but I've tried implementing things from various ones I've seen and haven't hit upon the right structure yet. I keep getting application/object defined errors. I'm sure it's the quotation marks but I can't seem to figure out where I have too many vs not enough. The end goal is for this formula to appear after a certain range of rows (newrows variable) and update dynamically as it fills down each row after that.
I've tried removing the extra quotes around the string sections, but it then gives me an "Expected: end of statement" error near the beginning of the code. When I'm viewing it in Notepad ++ it looks correct, but still get the errors in VBA itself when running the macro in excel.
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(""E""" & newrows & "= ""Planning"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,3,FALSE),IF(""E""" & newrows & "=""Fieldwork"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,4,FALSE),IF(""E""" & newrows & "=""Reporting"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,5,FALSE),IF(""E""" & newrows & "=""Wrap Up"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),IF(""E""" & newrows & "=""Proj. Mgmt"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),"""")))))"
The code is supposed to be looking up a value based on the value of a cell in a row (Column E) and the name of a project (Column D). The newrows is a numerical variable being generated by a dynamic list. The top portion of the sheet has existing tasks I don't want to overwrite but I want this formula in all cells after the end row of that list. I've been learning VBA as I've been working on this project so it may be something I just haven't come across yet so I appreciate the help!
ETA: This morning I tried turning the sequence with newrows into a variable with the column letter instead and got the same application defined error.
firstloc = "E" & newrows
secondloc = "D" & newrows
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
excel vba
Try...IF(E" & newrows ...
and repeat that change in other places.
– user10862412
Jan 3 at 21:39
2
ReplaceRange("R" & newrows & ":R1000").Formula =
withDebug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.
– Mathieu Guindon
Jan 3 at 21:41
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Every double-quote"
in the output string you want, needs to be escaped, i.e. doubled-up; soa = """"
is assigning a string literal that contains a single"
double-quote character. The reason for this is that the"
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.
– Mathieu Guindon
Jan 3 at 23:06
add a comment |
I know there's a bazillion of these questions out there; but I've tried implementing things from various ones I've seen and haven't hit upon the right structure yet. I keep getting application/object defined errors. I'm sure it's the quotation marks but I can't seem to figure out where I have too many vs not enough. The end goal is for this formula to appear after a certain range of rows (newrows variable) and update dynamically as it fills down each row after that.
I've tried removing the extra quotes around the string sections, but it then gives me an "Expected: end of statement" error near the beginning of the code. When I'm viewing it in Notepad ++ it looks correct, but still get the errors in VBA itself when running the macro in excel.
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(""E""" & newrows & "= ""Planning"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,3,FALSE),IF(""E""" & newrows & "=""Fieldwork"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,4,FALSE),IF(""E""" & newrows & "=""Reporting"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,5,FALSE),IF(""E""" & newrows & "=""Wrap Up"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),IF(""E""" & newrows & "=""Proj. Mgmt"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),"""")))))"
The code is supposed to be looking up a value based on the value of a cell in a row (Column E) and the name of a project (Column D). The newrows is a numerical variable being generated by a dynamic list. The top portion of the sheet has existing tasks I don't want to overwrite but I want this formula in all cells after the end row of that list. I've been learning VBA as I've been working on this project so it may be something I just haven't come across yet so I appreciate the help!
ETA: This morning I tried turning the sequence with newrows into a variable with the column letter instead and got the same application defined error.
firstloc = "E" & newrows
secondloc = "D" & newrows
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
excel vba
I know there's a bazillion of these questions out there; but I've tried implementing things from various ones I've seen and haven't hit upon the right structure yet. I keep getting application/object defined errors. I'm sure it's the quotation marks but I can't seem to figure out where I have too many vs not enough. The end goal is for this formula to appear after a certain range of rows (newrows variable) and update dynamically as it fills down each row after that.
I've tried removing the extra quotes around the string sections, but it then gives me an "Expected: end of statement" error near the beginning of the code. When I'm viewing it in Notepad ++ it looks correct, but still get the errors in VBA itself when running the macro in excel.
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(""E""" & newrows & "= ""Planning"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,3,FALSE),IF(""E""" & newrows & "=""Fieldwork"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,4,FALSE),IF(""E""" & newrows & "=""Reporting"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,5,FALSE),IF(""E""" & newrows & "=""Wrap Up"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),IF(""E""" & newrows & "=""Proj. Mgmt"",VLOOKUP(""D""" & newrows & ",DataCheck4!A:F,6,FALSE),"""")))))"
The code is supposed to be looking up a value based on the value of a cell in a row (Column E) and the name of a project (Column D). The newrows is a numerical variable being generated by a dynamic list. The top portion of the sheet has existing tasks I don't want to overwrite but I want this formula in all cells after the end row of that list. I've been learning VBA as I've been working on this project so it may be something I just haven't come across yet so I appreciate the help!
ETA: This morning I tried turning the sequence with newrows into a variable with the column letter instead and got the same application defined error.
firstloc = "E" & newrows
secondloc = "D" & newrows
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
excel vba
excel vba
edited Jan 4 at 13:31
M. R.
asked Jan 3 at 21:33
M. R.M. R.
64
64
Try...IF(E" & newrows ...
and repeat that change in other places.
– user10862412
Jan 3 at 21:39
2
ReplaceRange("R" & newrows & ":R1000").Formula =
withDebug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.
– Mathieu Guindon
Jan 3 at 21:41
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Every double-quote"
in the output string you want, needs to be escaped, i.e. doubled-up; soa = """"
is assigning a string literal that contains a single"
double-quote character. The reason for this is that the"
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.
– Mathieu Guindon
Jan 3 at 23:06
add a comment |
Try...IF(E" & newrows ...
and repeat that change in other places.
– user10862412
Jan 3 at 21:39
2
ReplaceRange("R" & newrows & ":R1000").Formula =
withDebug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.
– Mathieu Guindon
Jan 3 at 21:41
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Every double-quote"
in the output string you want, needs to be escaped, i.e. doubled-up; soa = """"
is assigning a string literal that contains a single"
double-quote character. The reason for this is that the"
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.
– Mathieu Guindon
Jan 3 at 23:06
Try
...IF(E" & newrows ...
and repeat that change in other places.– user10862412
Jan 3 at 21:39
Try
...IF(E" & newrows ...
and repeat that change in other places.– user10862412
Jan 3 at 21:39
2
2
Replace
Range("R" & newrows & ":R1000").Formula =
with Debug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.– Mathieu Guindon
Jan 3 at 21:41
Replace
Range("R" & newrows & ":R1000").Formula =
with Debug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.– Mathieu Guindon
Jan 3 at 21:41
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Every double-quote
"
in the output string you want, needs to be escaped, i.e. doubled-up; so a = """"
is assigning a string literal that contains a single "
double-quote character. The reason for this is that the "
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.– Mathieu Guindon
Jan 3 at 23:06
Every double-quote
"
in the output string you want, needs to be escaped, i.e. doubled-up; so a = """"
is assigning a string literal that contains a single "
double-quote character. The reason for this is that the "
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.– Mathieu Guindon
Jan 3 at 23:06
add a comment |
2 Answers
2
active
oldest
votes
Snake Formula
If D and E are column letters then
Sub SnakeFormula()
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(E" & newrows _
& "=""Planning"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,3,FALSE),IF(E" & newrows _
& "=""Fieldwork"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,4,FALSE),IF(E" & newrows _
& "=""Reporting"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,5,FALSE),IF(E" & newrows _
& "=""Wrap Up"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),IF(E" & newrows _
& "=""Proj. Mgmt"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),""""))))))"
End Sub
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
add a comment |
Think I finally got it working the way I intended. Thanks all for the help. It was missing a parentheses (Thanks VBasic2008). It also didn't seem to like the combination of a Column letter and the variable, so I did that outside of the sequence. Finally I removed the iferror and just used the if statement's else result to return null if none of the conditions were met.
Dim firstloc As String
Dim secondloc As String
Dim varloc As String
firstloc = "E" & newrows
secondloc = "D" & newrows
varloc = "R" & newrows
Range("R" & newrows & ":R1000").Formula = "=IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
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%2f54030098%2fvba-formula-application-object-defined-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Snake Formula
If D and E are column letters then
Sub SnakeFormula()
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(E" & newrows _
& "=""Planning"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,3,FALSE),IF(E" & newrows _
& "=""Fieldwork"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,4,FALSE),IF(E" & newrows _
& "=""Reporting"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,5,FALSE),IF(E" & newrows _
& "=""Wrap Up"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),IF(E" & newrows _
& "=""Proj. Mgmt"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),""""))))))"
End Sub
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
add a comment |
Snake Formula
If D and E are column letters then
Sub SnakeFormula()
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(E" & newrows _
& "=""Planning"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,3,FALSE),IF(E" & newrows _
& "=""Fieldwork"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,4,FALSE),IF(E" & newrows _
& "=""Reporting"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,5,FALSE),IF(E" & newrows _
& "=""Wrap Up"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),IF(E" & newrows _
& "=""Proj. Mgmt"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),""""))))))"
End Sub
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
add a comment |
Snake Formula
If D and E are column letters then
Sub SnakeFormula()
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(E" & newrows _
& "=""Planning"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,3,FALSE),IF(E" & newrows _
& "=""Fieldwork"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,4,FALSE),IF(E" & newrows _
& "=""Reporting"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,5,FALSE),IF(E" & newrows _
& "=""Wrap Up"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),IF(E" & newrows _
& "=""Proj. Mgmt"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),""""))))))"
End Sub
Snake Formula
If D and E are column letters then
Sub SnakeFormula()
Range("R" & newrows & ":R1000").Formula = "=iferror(IF(E" & newrows _
& "=""Planning"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,3,FALSE),IF(E" & newrows _
& "=""Fieldwork"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,4,FALSE),IF(E" & newrows _
& "=""Reporting"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,5,FALSE),IF(E" & newrows _
& "=""Wrap Up"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),IF(E" & newrows _
& "=""Proj. Mgmt"",VLOOKUP(D" & newrows _
& ",DataCheck4!A:F,6,FALSE),""""))))))"
End Sub
edited Jan 4 at 13:41
answered Jan 3 at 21:56
![](https://i.stack.imgur.com/1mtoT.jpg?s=32&g=1)
![](https://i.stack.imgur.com/1mtoT.jpg?s=32&g=1)
VBasic2008VBasic2008
3,5392517
3,5392517
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
add a comment |
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.
– M. R.
Jan 4 at 13:24
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.
– VBasic2008
Jan 4 at 13:37
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
I added a sixth, unfortunately still an application-defined error.
– M. R.
Jan 4 at 13:42
add a comment |
Think I finally got it working the way I intended. Thanks all for the help. It was missing a parentheses (Thanks VBasic2008). It also didn't seem to like the combination of a Column letter and the variable, so I did that outside of the sequence. Finally I removed the iferror and just used the if statement's else result to return null if none of the conditions were met.
Dim firstloc As String
Dim secondloc As String
Dim varloc As String
firstloc = "E" & newrows
secondloc = "D" & newrows
varloc = "R" & newrows
Range("R" & newrows & ":R1000").Formula = "=IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
add a comment |
Think I finally got it working the way I intended. Thanks all for the help. It was missing a parentheses (Thanks VBasic2008). It also didn't seem to like the combination of a Column letter and the variable, so I did that outside of the sequence. Finally I removed the iferror and just used the if statement's else result to return null if none of the conditions were met.
Dim firstloc As String
Dim secondloc As String
Dim varloc As String
firstloc = "E" & newrows
secondloc = "D" & newrows
varloc = "R" & newrows
Range("R" & newrows & ":R1000").Formula = "=IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
add a comment |
Think I finally got it working the way I intended. Thanks all for the help. It was missing a parentheses (Thanks VBasic2008). It also didn't seem to like the combination of a Column letter and the variable, so I did that outside of the sequence. Finally I removed the iferror and just used the if statement's else result to return null if none of the conditions were met.
Dim firstloc As String
Dim secondloc As String
Dim varloc As String
firstloc = "E" & newrows
secondloc = "D" & newrows
varloc = "R" & newrows
Range("R" & newrows & ":R1000").Formula = "=IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
Think I finally got it working the way I intended. Thanks all for the help. It was missing a parentheses (Thanks VBasic2008). It also didn't seem to like the combination of a Column letter and the variable, so I did that outside of the sequence. Finally I removed the iferror and just used the if statement's else result to return null if none of the conditions were met.
Dim firstloc As String
Dim secondloc As String
Dim varloc As String
firstloc = "E" & newrows
secondloc = "D" & newrows
varloc = "R" & newrows
Range("R" & newrows & ":R1000").Formula = "=IF(" & firstloc & "= ""Planning"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,3,FALSE),IF(" & firstloc & "=""Fieldwork"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,4,FALSE),IF(" & firstloc & "=""Reporting"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,5,FALSE),IF(" & firstloc & "=""Wrap Up"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),IF(" & firstloc & "=""Proj. Mgmt"",VLOOKUP(" & secondloc & ",DataCheck4!A:F,6,FALSE),"""")))))"
answered Jan 4 at 15:57
M. R.M. R.
64
64
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%2f54030098%2fvba-formula-application-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
GL6,wR,re Z,kty8j1e7zJ4N,3MQn9Xk,xc2QR4Id,i5M5Us8X7meyw8UPs2 lMl GXJ 4S8YJ,HuBDS2GFZjssVjHIrcLdIfYsTQ2,5c9pQH
Try
...IF(E" & newrows ...
and repeat that change in other places.– user10862412
Jan 3 at 21:39
2
Replace
Range("R" & newrows & ":R1000").Formula =
withDebug.Print
, then run it and see what gets printed into the immediate pane (Ctrl+G) - then take that output, copy it, and paste it into a cell - Excel will complain about the formula being invalid; fix the formula in Excel, then go back to the code and fix accordingly.– Mathieu Guindon
Jan 3 at 21:41
Hi Mathieu, it shows "" around the row call: IE If("E"23=....... However, if I remove that set of quotes I get the compile error expected end of statement.
– M. R.
Jan 3 at 21:48
Every double-quote
"
in the output string you want, needs to be escaped, i.e. doubled-up; soa = """"
is assigning a string literal that contains a single"
double-quote character. The reason for this is that the"
character happens to be the string literal delimiter token, so that character needs to be escaped somehow if it needs to appear in a string literal: in VBA you escape double quotes by doubling them, is all.– Mathieu Guindon
Jan 3 at 23:06