CODEWITHSUNDEEP
excelvba
jceast7
jceast7

Reputation: 19

I have a long equation with Range() and math operations. I get Compile error - Sub Function Not Defined

I get a

Compile Error - sub function not defined

in my equation which is only mathematical operations. Only 1 of the Range() operators is flagged. Range ("O8") at the end of the equation. For the love of me I cannot see why this would happen.

If Range("O8") = 0 Then
    Range("B10:K10)").Value = Null
Else
    Range("B10") = Range("B8") / Range("O8")
    Range("C10") = Range("C8") / Range("O8")
    Range("D10") = Range("D8") / Range("O8")
    Range("E10") = Range("E8") / Range("O8")
    Range("F10") = Range("F8") / Range("O8")
    Range("G10") = Range("G8") / Range("O8")
    Range("H10") = Range("H8") / Range("O8")
    Range("I10") = Range("I8") / Range("O8")
    Range("J10") = Range("J8") / Range("O8")
    Range("K10") = Range("K8") / Range("O8")
 End If

'MsgBox "Range (O8)=" & Range("O8")

Range("O15").Value = Round(IfError(141.5 / ((((141.5 / (Range("B15") +   131.5)) * Range("B8")) _
    + ((141.5 / (Range("C15") + 131.5)) * Range("C8")) + ((141.5 / (Range("D15") + 131.5)) * _
    Range("D8")) + ((141.5 / (Range("E15") + 131.5)) * Range("E8")) + ((141.5 / (Range("F15") + 131.5)) * _
    Range("F8")) + ((141.5 / (Range("G15") + 131.5)) * Range("G8")) + ((141.5 / (Range("H15") + 131.5)) * _
    Range("H8")) + ((141.5 / (Range("I15") + 131.5)) * Range("I8")) + ((141.5 / (Range("J15") + 131.5)) * _
    Range("J8")) + ((141.5 / (Range("K15") + 131.5)) * Range("K8"))) / _
    Range("O8")) - 131.5, 0), 4)

Upvotes: 0

Views: 34

Answers (2)

Dominique
Dominique

Reputation: 17565

In your macro, replace IfError by Application.WorksheetFunction.IfError (this is a general way to use worksheet functions within VBA code).
Normally, when you start typing Application.WorksheetFunction., the code completion should show you IfError as one of the possibilities.

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96791

Excel is telling you that IfError() is a function for a worksheet cell and not part pf the VBA function list.

  1. If you want to put a formula in the cell, then you must create a string that "looks like"" the formula you would have typed and insert it like: Range("O15").Formula="=SUM(A1+B1)"
  2. Only if you want to put a value in the cell should you try to do the calculation in VBA

Upvotes: 0

Related Questions