Application-defined or object-defined error when inserting a formula in VBA

Question

The following formula works great when I directly add it in excel, but when I add it to VBA I get a Run Time error: Application-defined or object-defined error.

This is the Formula I am inserting into the macro:

ActiveCell.FormulaR1C1 = "=VLOOKUP([[#This Row],[MATERIAL]],material!A$3:D$114,3)"

What am I doing wrong?

TableName = ActiveSheet.Previous.Name & "_Spider"

Range(TableName & "[RAWMATERIAL LENGTH]").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP([[#This Row],[MATERIAL]],material!A$3:D$114,3)"

Try using the macro recorder, I think that your syntax around [[#This Row],[MATERIAL]] is incorrect. The macro recorder should provide a suitable R1C1 formula to work with. — Sam
– Sam, Commented Nov 4, 2013 at 21:57
Use Debug.Print to output the formula to the Immediate pane in the VBE, and check it's what you're expecting. Copy/paste from there to the worksheet and check it's accepted. As @SamWard notes, your formula format looks wrong. — Tim Williams
– Tim Williams, Commented Nov 4, 2013 at 22:11

Blackhawk · Accepted Answer · 2013-11-04 22:42:16Z

2

I believe the issue is that when you set the formula with .FormulaR1C1 (msdn reference), you have to refer to cells by row and column numbers (for example =SQRT(R2C5)). You should try using .Formula (msdn reference) instead.

answered Nov 4, 2013 at 22:42

Blackhawk

6,1804 gold badges31 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Application-defined or object-defined error when inserting a formula in VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related