Maintaining formula while clearing the cell

[Sharad Vyas]

I am working with VSTO with C#. My worksheet has 10 Columns and many nows.
Some of the cell contains formula and other are populated with data from the
data source.I define formating, formulas and fill in all cells with 0s
(where there is no formula). I have to compute Meaid across the column for
each row. When I load the data, it dictates whether I will have 2 or 4 or
all 10 columns filled in. Once I load the data (lets say 4 columns),
programatically I clear the remaining 6 columns. All works fine until this
point. If I change some parameter and reload the data in the same session,
and lets say it has (5 columns) more than 4 columns this time, all the cells
, which had formula, for 5th columns, remains null. Data load is just fine.
The reason for this issue is that when I cleared the 6 columns during my
first data loas, it cleared my formula too.

My quesitons is how can I maitain/keep the formula, which I set in the
Excel, whiling Clearing the cell programtically. As I said I am using C#
with VSTO and using Excel object model;s Range.Clear() method to clear the
cell.

Help greatly appreiciated!!
-Sharad

 

[Bernie Deitrick]

Sharad,

Write your formulas to return "" if their input cell is blank:

You have
=Formula

Change to
=IF(B6<>"",Formula,"")

and then don't delete your formulas, perhaps by changing

Range.Clear()

to

Range.SpecialCells(xlCellTypeConstants).Clear()

HTH,
Bernie
MS Excel MVP

 

[Sharad Vyas]

Great.While trying this
Range.SpecialCells(xlCellTypeConstants).Clear();
Range.SpecialCells has two parameter second one is value of type object,
what should i use for that?

 

[Bernie Deitrick]

Sharad ,

Use this to clear numbers:
Range.SpecialCells(xlCellTypeConstants, xlNumbers).Clear();

or this to clear text:
Range.SpecialCells(xlCellTypeConstants, xlTextValues).Clear();

or this to clear numbers and text:
Range.SpecialCells(xlCellTypeConstants,xlNumbers + xlTextValues).Clear();


From help:

expression.SpecialCells(Type, Value)
expression Required. An expression that returns one of the objects in the Applies To list.
Type Required
XlCellType. The cells to include.
XlCellType can be one of these XlCellType constants.
xlCellTypeAllFormatConditions. Cells of any format
xlCellTypeAllValidation. Cells having validation criteria
xlCellTypeBlanks. Empty cells
xlCellTypeComments. Cells containing notes
xlCellTypeConstants. Cells containing constants
xlCellTypeFormulas. Cells containing formulas
xlCellTypeLastCell. The last cell in the used range
xlCellTypeSameFormatConditions. Cells having the same format
xlCellTypeSameValidation. Cells having the same validation criteria
xlCellTypeVisible. All visible cells

Value Optional Variant. If Type is either xlCellTypeConstants or xlCellTypeFormulas, this argument
is used to determine which types of cells to include in the result. These values can be added
together to return more than one type. The default is to select all constants or formulas, no matter
what the type. Can be one of the following XlSpecialCellsValue constants:
XlSpecialCellsValue can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues

HTH,
Bernie
MS Excel MVP

 

[Sharad Vyas]

Bernie,

TO test this out before making changes to App, I tried this:
D12= IF(D12<>"",AVERAGE(A12:C12),"")
However this is leading to circular reference problem. I might have
misunderstand what you are trying to suggest by saying:
You have
Reason I am doing all this is becuase if I don't clear the cell my Median
formula takes the content (0s) of non populated columns as well. Is there
way I can avoid including the hidden column in the formula? So lets say in
above example
D12 = Average(A12:C12). If I hide Column C with 0 content (defualt value I
am suing) it will still be included in the Average calculation, which is not
what I want. However if I Clear column C, my average will only include A and
B column.

Please clarify. Thanks
-Sharad

 

[lotus]

Bernie,

Reason I am doing all this is becuase if I don't clear the cell my
Median
formula takes the content (0s) of non populated columns as well. Is
there
way I can avoid including the hidden column in the formula? So lets say
in
above example
D12 = Average(A12:C12). If I hide Column C with 0 content (defualt
value I
am suing) it will still be included in the Average calculation, which
is not
what I want. However if I Clear column C, my average will only include
A and
B column.

Please clarify. Thanks
-Sharad

SHARAD, IF THIS IS YOUR ONLY PROBLEM TRY THE FOLLOWING FORMULA IN D12,
INSTEAD OF THE AVERAGE FORMULA.


*=SUM(A12:C12)/(COUNTIF(A12:C12,"<>0"))*

 

[Peter Huang [MSFT]]

Hi Bernie,

Have you tried Lutos's suggestion?
If you still have any concern, please feel free to post here.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.