Insert 0 in blank cells

[Steve Brown]

I don't have much experience with Excel. I have a large spreadsheet with many blank cells. I want to insert a zero in every blank cell. I tried some IF statements, but they were not successful. Any help would be greatly appreciated.

Steve Brown

 

[Michael M]

What formula are you using currently in those blank cells ?
You could also try this
File>>Options>>Advanced>>Display options for this worksheet>>tick the box "Show a zero in cells that have a zero value"

 

[Steve Brown]

Thanks for your prompt reply!

The "show a zero in cells that have a zero value" box is already checked.
I'll have to recreate the IF statement that I tried.

 

[Michael M]

Ok, can you post that IF formula ?

 

[Steve Brown]

I'm working on it...

 

[Steve Brown]

The first 3 rows are not relevant to this issue. Starting in row 4 I want to check if the cell is blank. If the cell is blank, insert the value 0. If the cell is not blank, move onto the next cell in the column.

Here it is: =IF(B4="",B4=0)

It works fine for cell B4, but if I paste the formula into the rest of the column, I get all zeroes. I thought it was checking the value in b4, but after applying the IF statement to b4, the value in b4 is zero, not blank, so that's not what's happening.

 

[Michael M]

If you want to check col "B" you will need VBA
Try using

Sub MM1()
 Dim r As Long
 For r = 4 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(4, "B") = "" Then Cells(4, "B") = "0"
Next r
End Sub

 

[Steve Brown]

If you want to check col "B" you will need VBA
Try using

Sub MM1()
Dim r As Long
For r = 4 To Cells(Rows.Count, "B").End(xlUp).Row
    If Cells(4, "B") = "" Then Cells(4, "B") = "0"
Next r
End Sub

Great, I want to try this (neophyte speaking here). How do I apply it?

 

[Michael M]

To insert the code
Press Alt + F11>>In the LH window select "This workbook" then paste the code in the RH window where the cursor is flashing.
To run the code on the activesheet Press ALt + F8 and select MM1 from the list provided and press "Run"

 

[Steve Brown]

Sorry to be so obtuse, but when I press alt F11, the computer wants to lower its volume.