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

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

[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?

Post by Bernie Deitrick
Sharad,
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

Post by 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 ,

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

Post by 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?

Post by Bernie Deitrick
Sharad,
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

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

[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

Post by Bernie Deitrick

Post by Sharad Vyas

Post by Bernie Deitrick
=Formula
Change to
=IF(B6<>"",Formula,"")

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

Post by Bernie Deitrick
Sharad ,
Range.SpecialCells(xlCellTypeConstants, xlNumbers).Clear();
Range.SpecialCells(xlCellTypeConstants, xlTextValues).Clear();
Range.SpecialCells(xlCellTypeConstants,xlNumbers + xlTextValues).Clear();
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 can be one of these XlSpecialCellsValue constants.
xlErrors
xlLogical
xlNumbers
xlTextValues
HTH,
Bernie
MS Excel MVP

Post by 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?

Post by Bernie Deitrick
Sharad,
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

Post by 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

[lotus]

Post by Sharad Vyas
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.