CODEWITHSUNDEEP
vbaexcel
duberry
duberry

Reputation: 41

Excel VBA Formula - 1004 object or application defined error

I'm adding a macro to allow the user to change the formula in a cell. Here is the code that added to a module:

Sub OverrideCharge()

title = "Override Total Labor Cost"

'Ask user if they want to override
overrideMsg = MsgBox("Override Total Labor Cost with 4-Hour Minimum Charge?", vbYesNo, title)
If overrideMsg = vbYes Then
    Sheets("Totals").Activate
    Range("L25").Formula = "=SUM(IF(MOD(ROW(INDIRECT(""L11:L""&ROW()-1)),2)=1,INDIRECT(""L11:L""&ROW()-1),0)))"
Else: overrideMsg = vbNo
    Exit Sub
End If

End Sub

I get the error when defining the Range("L25").Formula. I don't know if it's a syntax error or if I'm referencing the sheet/cell incorrectly.

Upvotes: 0

Views: 881

Answers (2)

Shai Rado
Shai Rado

Reputation: 33672

When working with long complex formulas I like adding a FormulaString variable to help me debug (this way I can see better where the error is coming from).

Also, there is no need to Activate the Sheets("Totals"), and then placing the formula in Cell L25, you can do it directly with Sheets("Totals").Range("L25").Formula.

My own preference, instead of starting to count how many " , or "" , or maybe """" I need, I like working with Chr(34).

Code

Sub OverrideCharge()

Dim FormStr As String
Title = "Override Total Labor Cost"

'Ask user if they want to override
overrideMsg = MsgBox("Override Total Labor Cost with 4-Hour Minimum Charge?", vbYesNo, Title)
If overrideMsg = vbYes Then
    ' use a String for Formula to help debug it (before trying to put it inside a cell)
    FormStr = "=SUM(IF(MOD(ROW(INDIRECT(" & Chr(34) & "L11:L" & Chr(34) & "&ROW()-1)),2)=1,INDIRECT(" & Chr(34) & "L11:L" & Chr(34) & "&ROW()-1),0))"
    Debug.Print FormStr '<-- for debug only
    Sheets("Totals").Range("L25").Formula = FormStr
Else
    overrideMsg = vbNo
    Exit Sub
End If

End Sub

Upvotes: 0

Delete Me
Delete Me

Reputation: 102

You need to get your quote marks & brackets sorted, should be:

Range("L25").Formula = "=SUM(IF(MOD(ROW(INDIRECT(""L11:L""&(ROW()-1))),2)=1,INDIRECT(""L11:L""&(ROW()-1)),0))"

Upvotes: 0

Related Questions