VBA Formula Application/Object Defined Error

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















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),"""")))))"









share|improve this question

























  • Try ...IF(E" & newrows ... and repeat that change in other places.

    – user10862412
    Jan 3 at 21:39






  • 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











  • 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




















0















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),"""")))))"









share|improve this question

























  • Try ...IF(E" & newrows ... and repeat that change in other places.

    – user10862412
    Jan 3 at 21:39






  • 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











  • 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
















0












0








0








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),"""")))))"









share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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





    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













  • 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





















  • Try ...IF(E" & newrows ... and repeat that change in other places.

    – user10862412
    Jan 3 at 21:39






  • 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











  • 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



















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














2 Answers
2






active

oldest

votes


















1














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





share|improve this answer


























  • 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



















0














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),"""")))))"





share|improve this answer
























    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
    });


    }
    });














    draft saved

    draft discarded


















    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









    1














    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





    share|improve this answer


























    • 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
















    1














    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





    share|improve this answer


























    • 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














    1












    1








    1







    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





    share|improve this answer















    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






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jan 4 at 13:41

























    answered Jan 3 at 21:56









    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



















    • 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













    0














    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),"""")))))"





    share|improve this answer




























      0














      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),"""")))))"





      share|improve this answer


























        0












        0








        0







        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),"""")))))"





        share|improve this answer













        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),"""")))))"






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Jan 4 at 15:57









        M. R.M. R.

        64




        64






























            draft saved

            draft discarded




















































            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.




            draft saved


            draft discarded














            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





















































            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
            MI,fifthw5TCCMC,7W7fNzRKmYkpsbFIR2ZJZA7w

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas