Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
On Sep 17, 1:23*pm, shriil wrote:
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." |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
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 ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/WordPress/ On 9/17/2010 2:23 PM, shriil wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Substituting zero values in blank cells
On Sep 18, 9:50*am, Javed wrote:
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Blank Cells vs. Zero Values | Excel Worksheet Functions | |||
Graphing 0 values from blank cells | Excel Discussion (Misc queries) | |||
If certain cells not blank, and cells in range are, set values to | Excel Discussion (Misc queries) | |||
Substituting values | Excel Discussion (Misc queries) | |||
Finding values within text and substituting with alternate values. | Excel Programming |