VBA Formula Application/Object Defined Error

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

Try ...IF(E" & newrows ... and repeat that change in other places. — user10862412
– user10862412, Commented Jan 3, 2019 at 21:39
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
– Mathieu Guindon, Commented Jan 3, 2019 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.
– M. R., Commented Jan 3, 2019 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
– Mathieu Guindon, Commented Jan 3, 2019 at 23:06

VBasic2008 · Accepted Answer · 2019-01-04 13:41:39Z

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

edited Jan 4, 2019 at 13:41

answered Jan 3, 2019 at 21:56

VBasic2008

56.8k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M. R. Over a year ago

Hello, thanks for this, unfortunately it gives me the same application defined error I get when I removed the extraneous "" before.

VBasic2008 Over a year ago

@M. R. I think you might be missing one closing parenthesis. Add the sixth (6th) for iferror.

M. R. Over a year ago

I added a sixth, unfortunately still an application-defined error.

M. R. · Accepted Answer · 2019-01-04 15:57:18Z

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

Collectives™ on Stack Overflow

VBA Formula Application/Object Defined Error

2 Answers 2

Snake Formula

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Snake Formula

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related