使用代码输入IFERROR公式时出现Application-defined or object-defined error求助

Hey there! Let's dig into that IFERROR formula error you're hitting in VBA. I've run into this exact issue multiple times, so let's break down the most common culprits and their fixes:

1. Mishandled Quotation Marks in the Formula #

VBA treats double quotes as string delimiters, so if your IFERROR formula includes text values wrapped in quotes (like "Not Found"), you need to escape those inner quotes with an extra double quote.

Wrong Code (Breaks):

Range("D1").Formula = "=IFERROR(VLOOKUP(A1,B:C,2,0),"Not Found")"

This fails because the first inner quote ends the VBA string prematurely.

Fixed Code:

Range("D1").Formula = "=IFERROR(VLOOKUP(A1,B:C,2,0),""Not Found"")"

2. Invalid Range References (Especially Sheet Names) #

If your formula references a sheet with spaces or special characters, you need to wrap the sheet name in single quotes—and escape those single quotes in VBA by doubling them.

Wrong Code (Breaks):

Range("D1").Formula = "=IFERROR(VLOOKUP(A1,Product List!B:C,2,0),""Not Found"")"

Fixed Code:

Range("D1").Formula = "=IFERROR(VLOOKUP(A1,'Product List'!B:C,2,0),""Not Found"")"

3. Mismatched Formula Notation Property #

If you're using R1C1-style references (like RC[-3]) but use .Formula instead of .FormulaR1C1, VBA will throw this error.

Wrong Code (Breaks):

Range("D1").Formula = "=IFERROR(VLOOKUP(RC[-3],R[1]C[1]:R[10]C[2],2,0),""Not Found"")"

Fixed Code:

Range("D1").FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-3],R[1]C[1]:R[10]C[2],2,0),""Not Found"")"

4. Protected Worksheet/Cell #

If the cell you're trying to write the formula to is locked (and the sheet is protected), VBA can't modify it.

Fix:
Either unlock the cell first, or temporarily disable protection:

' Option 1: Unlock the specific cell
Range("D1").Locked = False
' Option 2: Temporarily turn off sheet protection
ActiveSheet.Unprotect Password:="yourPasswordHere"

' Write your formula
Range("D1").Formula = "=IFERROR(VLOOKUP(A1,B:C,2,0),""Not Found"")"

' Re-protect if needed
ActiveSheet.Protect Password:="yourPasswordHere"

5. Regional Setting Conflicts #

Some regions use semicolons (;) instead of commas (,) as formula separators. If your code uses commas but your system expects semicolons, use .FormulaLocal instead of .Formula.

Fixed Code for Regional Settings:

Range("D1").FormulaLocal = "=IFERROR(VLOOKUP(A1;B:C;2;0);""Not Found"")"

If none of these resolve the issue, sharing your exact VBA code line would help pinpoint the problem even faster!

内容的提问来源于stack exchange，提问作者Ravarro