If formula results in an error, how do I make a zero appear

[dpaton05]

I have a formula. If the result is an error, how do I make a 0 appear?

=IF(E5="Activities",0,[Price 
ex. GST]*0.1)

I tried this but it won't work

=IFERROR(IF(E5="Activities",0,[Price 
ex. GST]*0.1),0)

 

[AlanY]

try

=IFERROR(IF(E5="Activities",0,[Price
ex. GST]*0.1),"")

 

[dpaton05]

That doesn't work. If I change the width of the column, it changes what part of the formula that is visible

Here are some screenshots, all with the same formula, just changed column(K) width.

Width 14

width 16

width 19.14

 

[Michael M]

Is the table name
[Price ex. GST]
correct ???

 

[dpaton05]

That is the name of one of the columns

 

[dpaton05]

After further testing I have found that I get the #VALUE! error in those K5 and L5 (first row of my table) if I run the following code to delete all lines, I get the displayed image

Sub CostingDeleteAll()
    'Deleting The Data In A Table
    Dim tbl As ListObject
    Dim cell As Range
        Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
        'Delete all table rows except first row
        With tbl.DataBodyRange
            If .Rows.Count > 1 Then
                .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
            End If
            'Clear the contents, but not delete the formulas
            For Each cell In tbl.ListRows(1).Range.Cells
                If Not cell.HasFormula Then
                    cell.Value = ""
                End If
            Next
        End With
End Sub

CSS_quoting_tool_31.8.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2	23/10/2020	#######
4	Date	Purchase order #	Quote Ref #	Name	Service	Requesting Organisation	Caseworker Name	Allocated to	Wait Time/Hrs	Price ex. GST	GST	Price inc. GST	Date report received	Date report sent	Allocated by	Report sent by
5											#VALUE!	#VALUE!
6
Costing_tool

Cell Formulas
Range	Formula
A2:B2	A2	=TODAY()
K5	K5	=IF(E5="Activities",0,[Price ex. GST]*0.1)
L5	L5	=IF(E5="Activities",[@[Price ex. GST]],[GST]+[Price ex. GST])

Cells with Data Validation
Cell	Allow	Criteria
D5,A5,G5:I5	Any value
G5:I5	Any value

But if I run the following code to delete 1 line, the resulting table will look like this

Sub DeleteCostingLine()
    Dim ws As Worksheet
    Dim tbl As ListObject
        Set ws = ActiveSheet
        Set tbl = ws.ListObjects("tblCosting")

    tbl.ListRows(tbl.ListRows.Count).Delete
    'Worksheets("Costing_tool").Range("AB5").Value = "1"
End Sub

CSS_quoting_tool_31.8.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	23/10/2020	#######
4	Date	Purchase order #	Quote Ref #	Name	Service	Requesting Organisation	Caseworker Name	Allocated to	Wait Time/Hrs	Price ex. GST	GST	Price inc. GST	Date report received	Date report sent	Allocated by
5
6
Costing_tool

Cell Formulas
Range	Formula
A2:B2	A2	=TODAY()

Cells with Data Validation
Cell	Allow	Criteria
D5,A5,G5:I5	Any value
G5:I5	Any value

Why does one make nothing appear in the formula cells and the other give me a #VALUE! error when they are supposed to do very similar things. I do not want the error to appear.

 

[Michael M]

But shouldn't the PriceexGST have a cell reference ??

 

[dpaton05]

But shouldn't the PriceexGST have a cell reference ??

It is the name of the column. It is not using cell references, but it is a specific excel table and it is using table references.

 

[dpaton05]

Any ideas on my code Michael?