empty cells issue - possible formatting or formula issue

[lezawang]

Hi

I have a file which has 2 sheets. One is called FORM and another called Details. in the Details sheet, in the second cell, I have the following formula:

=IF(FORM!A2<>0,FORM!A2,IF(FORM!B2<>0,-FORM!B2,0))

Everything is working fine. I created these tables by myself in a new file but I have similar situation with a real file (not mine) which has exactly the same 2 sheets. What is the problem with the real file, if I do not put 0 in the "debit" then the Result column (first 5 cells) will be empty. I checked the format of all cells, Debit and Credit columns are type of Custom, while the Result column is type of Number. I tried to change all to different formats but could not get it work

FORM sheet
[TABLE="class: grid, width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]debit[/TD]
[TD="width: 64"]credit[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Details Sheet

The formula in the second cell is =IF(FORM!A2<>0,FORM!A2,IF(FORM!B2<>0,-FORM!B2,0))

[TABLE="class: grid, width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]result[/TD]
[/TR]
[TR]
[TD="align: right"]-10[/TD]
[/TR]
[TR]
[TD="align: right"]-20[/TD]
[/TR]
[TR]
[TD="align: right"]-30[/TD]
[/TR]
[TR]
[TD="align: right"]-40[/TD]
[/TR]
[TR]
[TD="align: right"]-50[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD="align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]

 

[phmalu]

This is very weird because in Excel there is no such a thing as "empty". An empty cell is always interpreted as 0. Could you share your .xlsx with us?

 

[lezawang]

Thanks for the reply. The file link is below. The sheet I am talking about is called Journal Details and the first 5 cells in column G are empty. they should not be empty. They should have values like -100,000, 566.03, etc. Thank you again


https://drive.google.com/file/d/1thBayXKOsguyeThkvc8vK2269BZeh_bJ/view?usp=sharing

 

[phmalu]

It's very weird because once I insert 0 on the 'Debit' column it starts calculating correctly, even if I delete it afterwards. Seems like a bug or formatting problem, have never seen this before.
----------------
Bingo, just figured out your problem! Those problematic cells are not empty, but with several blank spaces in ea. Once those are deleted it'll detect as zero and calculate correctly according to your formula.

 

[MARK858]

That is because the cells in column C of sheet Journal Entry Form aren't blank, they have a character 32 in the cells.

 

[MARK858]

Sorry should have posted a solution either run one of the macro's below to clean up the data

Sub clear32()
    Dim myRng As Range, myCell As Range
    Set myRng = Sheets("Journal Entry Form").Range("C9:C" & Sheets("Journal Entry Form").Range("C" & Rows.Count).End(xlUp).Row)
    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                                 myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0
End Sub

or

Sub clear32_2()
Sheets("Journal Entry Form").Range("C9:C" & Sheets("Journal Entry Form").Range("C" & Rows.Count).End(xlUp).Row).Replace What:=" ", Replacement:="", LookAt:=xlWhole
End Sub

or change the formula to

=IF(TRIM('Journal Entry Form'!C9)<>"",TRIM('Journal Entry Form'!C9),IF('Journal Entry Form'!D9<>0,-'Journal Entry Form'!D9,0))

 

[MARK858]

Actually looking at it more closely (although it doesn't apply to the data you posted in post number 1) you should also apply a trim or replace to Column D as well.

 

[lezawang]

Thank you all for all the help. Trim solution fixed the problem. I just like to know, how did you know there were Char 32 in the cells? Thank you once again

 

[MARK858]

Trim solution fixed the problem. I just like to know, how did you know there were Char 32 in the cells?

First you test to see if the cell is truly blank with

=ISBLANK(C9)

If it gives FALSE then you use

=CODE(C9)

to give you the ASCII number of the leftmost character in the cell.

As for the "Trim solution" you don't say if you are using the formula or the VBA. As I stated although your data in post one doesn't show it you also need to account for column D as it has the same issue.

In formula...

=--IF(TRIM('Journal Entry Form'!C9)<>"",TRIM('Journal Entry Form'!C9),IF(TRIM('Journal Entry Form'!D9)<>"",-TRIM('Journal Entry Form'!D9),0))

In VBA...

Sub clear32()
    Dim myRng As Range, myCell As Range
    Set myRng = Sheets("Journal Entry Form").Range("C9:D" & Sheets("Journal Entry Form").Range("A" & Rows.Count).End(xlUp).Row)
    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                                 myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0
End Sub

 

[lezawang]

Thank you thank you once again. I used this formula

=--IF(TRIM('Journal Entry Form'!C9)<>"",TRIM('Journal Entry Form'!C9),IF(TRIM('Journal Entry Form'!D9)<>"",-TRIM('Journal Entry Form'!D9),0))

but I am just curious now, why you decided to put -- infront of the formula? Thank you so much.