CODEWITHSUNDEEP
vbaexcelexcel-formula
Pericles Faliagas
Pericles Faliagas

Reputation: 636

How to leave cells blank when their value is 0 or does not exist in formula

How to tell the macro to leave the cell blank and not put a zero if the value of the cell is 0 or does not exist? In other types of formulas, I just added ,""-"")" at the end but in this case it does not work...

.Range("Z" & i & ":AB" & i).Formula = "=SUMIF($D$2:$D$" & lastrow & ",$Y" & i & ",E$2:E$" & lastrow & ")/" & dic(.Range("Y" & i).Value)

An example of a working occasion is this 

.Range("AD" & i).Formula = "=IF(INDEX($I$2:$I$" & lastrow & ",MATCH($Y" & i & ",$D$2:$D$" & lastrow & ",0))=0,Z" & i & ",""-"")"

Upvotes: 0

Views: 685

Answers (2)

Smith
Smith

Reputation: 98

Your formula in the example that does not work does not include the same conditional as your example of a working formula.

Your second formula is, essentially, "if this value is 0, then do X". Your first formula only has the SUMIF formula, it does not evaluate if the value returned is 0.

You should add the same logic to your SUMIF formula, to get the same result. Specifically, you need to ad an "IF" formula to this. Here's an example I have not tested, but should be more or less accurate.

.Range("Z" & i & ":AB" & i).Formula = "=if(SUMIF($D$2:$D$" & lastRow & ",$Y" 
& i & ",E$2:E$" & lastRow & ")/" & dic(.Range("Y" & i).Value) & "=0,""""," & 
"SUMIF($D$2:$D$" & lastRow & ",$Y" & i & ",E$2:E$" & lastRow & ")/" & 
dic(.Range("Y" & i).Value) & ")"

This will build the formula to check for 0s. If you want VBA to check for 0s, that will be a different solution.

Update for your comment:

I think this is what you're looking for, although I think your formula has an error in it.

"=IF($Z$2:$Z$" & lastRow & "*$AC$2:$AC" & lastRow & "=0,""-""," & "$Z$2:$Z$" 
& lastRow & "*$AC$2:$AC" & lastRow & ")"

Upvotes: 1

Matteo NNZ
Matteo NNZ

Reputation: 12665

There are too many information missing I think, but if I understand that you want to use VBA to add a built-in function into your cell (SUMIF) but you want this SUMIF to return "" instead of 0 (which is the normal return type, since the function is supposed to return a number), then what you can do is to build a nested function saying that if the result of your function is different than zero then you write that result, otherwise you write "": 

.Range("Z" & i & ":AB" & i).Formula = "=IF(SUMIF($D$2:$D$" & lastrow & ",$Y" & i & ",E$2:E$" & lastrow & ")/" & dic(.Range("Y" & i).Value) & "<> 0, SUMIF($D$2:$D$" & lastrow & ",$Y" & i & ",E$2:E$" & lastrow & ")/" & dic(.Range("Y" & i).Value), "")

... but if you give us more information there's probably a better way to get the result (probably a user-function instead of the built-in SUMIF directly put in the cell).

Upvotes: 1

Related Questions