Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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" wrote in message ... 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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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 =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 "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
Sharad Vyas Wrote: 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"))* -- lotus ------------------------------------------------------------------------ lotus's Profile: http://www.excelforum.com/member.php...o&userid=23759 View this thread: http://www.excelforum.com/showthread...hreadid=493829 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Maintaining formula while clearing the cell
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing content from a cell (or a group) but retain its formula | Excel Discussion (Misc queries) | |||
Maintaining a formula in a cell when there was an insert row | Excel Discussion (Misc queries) | |||
Maintaining Formula after adding a new row | New Users to Excel | |||
clearing a cell and keeping the formula | New Users to Excel | |||
Copy a formula maintaining a constant cell | Excel Worksheet Functions |