CODEWITHSUNDEEP
vbaexcelexcel-formulaformula
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Leave cell empty if formulas result is 0

I want for the formula to leave the cell empty if there is no value... When I run this part it says there is an error.. I am sure the mistake is in

.Formula = "=IFERROR(E2*G2),"""")"

The entire part of the code is

With Range("H2:H" & LastRow)
    .Formula = "=IFERROR((E2*G2),"""")"
    .Value = .Value
End With

Upvotes: 1

Views: 161

Answers (2)

Mrig
Mrig

Reputation: 11727

Ideally, instead of

.Formula = "=IFERROR((E2*G2),"""")"

you should use

.Formula = "=IF(IFERROR((E2*G2),"""")=0,"""",IFERROR((E2*G2),""""))"

because this will handle Error as well as 0. Hence as per comment your formula will be

.Formula = "=IF(IFERROR(INDEX(I:I,MATCH(VALUE(A2),E:E,FALSE)),"""")=0,"""",IFERROR(INDEX(I:I,MATCH(VALUE(A2),E:E,FALSE)),""""))"

Upvotes: 1

SJR
SJR

Reputation: 23081

Maybe this

With Range("H2:H" & LastRow)
    .Formula = "=IF(E2*G2=0,"""",e2*g2)"
    .Value = .Value
End With

Upvotes: 2

Related Questions