Substituting zero values in blank cells

[shriil]

I have an excel table which gets populated from another excel table.
After import the table looks like:

A1 B1
C1 O1
budget head department apr may jun jul aug sep
oct nov .... total

2a ops 11.2 0.4
1.1 4.2 16.9

2b mtc 3.1 0.7 5.5
8.6 1.2 0.5 19.6

... ... ,, ... ... ... ..

The table range is from A1: O50

As can be seen, some of the cells are blank as there was no data from
the imported table. I would like to convert these blank cells to zero
(0), instead of keeping them blank. This is because I use these table
for finding out individual department cost -budget head wise, for
which I use the sumproduct function. What I observe that while
evaluating a sumproduct formula, when the formula is encountering a
blank cell, it is giving an error (#Value), whereas when the blank
cell is replaced by a zero, the formula is working.

It would be great if I could run a macro or a code where the blank
cells in the range are looked up and replaced with the value 0.

Thanks for the help

 

[Don Guillett Excel MVP]

I have an excel table which gets populated from another excel table.
After import the table looks like:

         A1                   B1
C1                                                              O1
budget head        department       apr   may  jun   jul   aug   sep
oct   nov ....  total

          2a                ops              11.2         0.4
1.1           4.2                       16.9

          2b                 mtc                      3.1   0.7   5.5
8.6    1.2           0.5        19.6

           ...                  ...                ,,         ...                     ...                   ...       ..

The table range is from A1: O50

As can be seen, some of the cells are blank as there was no data from
the imported table. I would like to convert these blank cells to zero
(0), instead of keeping them blank. This is because I use these table
for finding out individual department cost -budget head wise, for
which I use the sumproduct function. What I observe that while
evaluating a sumproduct formula, when the formula is encountering a
blank cell, it is giving an error (#Value), whereas when the blank
cell is replaced by a zero, the formula is working.

It would be great if I could run a macro or a code where the blank
cells in the range are looked up and replaced with the value 0.

Thanks for the help

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

 

[dksaluki]

If you don't mind having 0's in the table, I'd just make a macro to
fill in the table with 0's in the empty cells.
Your macro might look something like this:

Sub Fill_in_zeros()
dim myRange as range
dim cell as range

myRange = InputBox("Select the table to fill in zeros",,,8,)
(you'll have to figure this part out to set it as a range of cells)
For each cell in myRange
if IsEmpty(cell) then cell.Value = 0
Next cell

End Sub

 

[Jon Peltier]

Select the range containing the table with blank cells.
Press Ctrl+G (GoTo).
Click Special.
Select Blanks and click OK.
Type a zero, then hold Ctrl while pressing Enter.

- Jon

 

[Javed]

I am combining the suggestions of Jon & DK


Sub Fill_in_zeros()

Dim myRange As Range

Set myRange = Application.InputBox(Prompt:="Select the table to
fill in zeros", Type:=8)

myRange.SpecialCells(xlCellTypeBlanks).Value = 0

End Sub

 

[shriil]

I am combining the suggestions of Jon & DK

Sub Fill_in_zeros()

    Dim myRange As Range

    Set myRange = Application.InputBox(Prompt:="Select the table to
fill in zeros", Type:=8)

    myRange.SpecialCells(xlCellTypeBlanks).Value = 0

End Sub

Thanks for the help. I think i will go with the macro examples given
by you guys..