VBA Object Defined Error

Question

I am getting a Run-Time Error '1004' based on the bottom line of code. If I "end" and Re-run, the problem does not come up.

I have only included the relevant code as I have a lot of variables, if's and formatting code.

Dim Yeild As String
Sub NewPage()
              '...
Yeild = "=INDEX(AcreGrid,MATCH(" & Chr(34) & Comm & Chr(34) & _
          ",Prod!R3C1:R30C1,),MATCH(" & Chr(34)  & Season & Chr(34) & _
          ",Prod!R3C1:R3C16,))/" & ActiveCell.Offset(0, 3)
              '... the above formula has been giving me the most problems
    ActiveCell.Offset(0, 3).Formula = "=Vlookup(B" & ActiveCell.Row & ", EQF,5,)" 'Factor
               '... I didn't define the above formula because it was causing problems
    ActiveCell.Offset(0, 7) = Yeild
               '...
 End Sub

I have also tried the following but the Cell Reference B4 changes to 'B4'; causes a #NAME? error in Excel.

Yeild = "=INDEX(AcreGrid,MATCH(" & Chr(34) & Comm & Chr(34) & _
        ",Prod!R3C1:R30C1,),MATCH(" & Chr(34)  & Season & Chr(34) & _
        ",Prod!R3C1:R3C16,))/Vlookup(B" & ActiveCell.Row & ", EQF,5,)"
ActiveCell.Offset(0, 7) = Yeild

Any help would be greatly appriciated.

Move Dim Yeild As String into the sub. I would also put Option Explicit at the top of your module... — enderland
– enderland, Commented Aug 29, 2013 at 16:00
What's actually in Yeild? Its probably not what you think. Use a BreakPoint or Debug.Print Yeild to see what's actually there before you try to assign it into a Formula. — RBarryYoung
– RBarryYoung, Commented Aug 29, 2013 at 17:17

AndASM · Accepted Answer · 2013-08-29 17:12:43Z

2

Minor stuff

Okay, first thing some nitpicking. I suggest being explicit and assign to .Formula or .FormulaR1C1 instead of leaving it implied as you do on this line:

ActiveCell.Offset(0, 7) = Yeild

Likely Source of Error Message

Now for your actual problem. There are many places where the following line could go wrong and produce an invalid formula

Yeild = "=INDEX(AcreGrid,MATCH(" & Chr(34) & Comm & Chr(34) & ",Prod!R3C1:R30C1,),MATCH(" & Chr(34) & Season & Chr(34) & ",Prod!R3C1:R3C16,))/" & ActiveCell.Offset(0, 3)

An easy example, if the last part of the statement ActiveCell.Offset(0, 3) returns blank or an error value you end up with a formula divided by nothing, or an error:

=INDEX(AcreGrid,MATCH("",Prod!R3C1:R30C1,),MATCH("",Prod!R3C1:R3C16,))/

That is not a valid formula because of the trailing division / at the end. So Excel gives you Run-time error 1004, which in this case means the same thing as the "Microsoft Excel found an error in the formula you entered." message, only less helpful or descriptive.

Another source of trouble could be if Comm or Season include any double quotes ".

edited Aug 29, 2013 at 17:12

answered Aug 29, 2013 at 17:07

AndASM

10.7k1 gold badge25 silver badges33 bronze badges

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

1 Comment

user2719198 Over a year ago

Thanks, I fixed it. I had to move the order of variable definitions and activecell references. I was also missing application.calculate.

Collectives™ on Stack Overflow

VBA Object Defined Error

1 Answer 1

Minor stuff

Likely Source of Error Message

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Minor stuff

Likely Source of Error Message

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related