CODEWITHSUNDEEP
excelvba
anishmbait
anishmbait

Reputation: 31

'Format' function returns Run Time Error 424

Without the format function, the below code gives me the TNPS score correctly, but I'd like the format in percent with two decimal points.

The Format Function is producing error

Run Time error '424' Object Required.

I referred to a possible duplicate: Getting error 424 yet could not solve it.

Public Function tnps(promoter As Integer, passive As Integer, detractor As Integer)
    tnps = (promoter - detractor) / (promoter + passive + detractor)
End Function


Sub main()

Dim pro As Integer
Dim pass As Integer
Dim det As Integer

For i = 2 To 25
    pro = Cells(i, 2).Value
    pass = Cells(i, 3).Value
    det = Cells(i, 4).Value
    Format(Cells(i, 5), "Percent") = tnps(pro, pass, det)
Next i
End Sub

Upvotes: 0

Views: 98

Answers (2)

Sefan
Sefan

Reputation: 709

Format should be used for a "Expression" and not cells.

You should use Range.NumberFormat like this:

Cells(i, 5) = tnps(pro, pass, det)
Range(Cells(i, 5)).NumberFormat = "0.00%"

Upvotes: 2

Manish
Manish

Reputation: 213

Change the following code to

Format(Cells(i, 5), "Percent") = tnps(pro, pass, det)

to

Cells(i, 5) = Format(tnps(pro, pass, det), "0.00%")

It will work.

Upvotes: 1

Related Questions