PDA

View Full Version : Deleting a formula from a cell once the result is given



jpolzner
04-14-2007, 02:28 PM
Is there a way to have a formula removed from a cell immediately upon the formula placing the result into the cell, yet, still retain the result?

My problem is that I have a formula that calculates a result, based upon the current date. If the particular cell is dated 4-4-07 and it's only 4-3-07, the result enters the preceding cell. Great, I can accomplish that.....But, since the formula is based upon my using TODAY(), once the midnight hour occurs, the formula that entered the correct result in the cell dated for 4-3-07, now removes that result and leaves the cell blank, because, now, the date is 4-4-07............I hope I'm explaining this clearly.

Does this require some kind of Macro or VBA Code? Any help would be really appreciated......I'm really stumped.............

Thanks!!!!!!!!

- Jim

asingh
04-14-2007, 10:28 PM
Hi,
If you manually want to do it..than just do a paste - special - values on the cell..after you are satisfied with the solution...If you want to automate..then VBA would be a good way to go about it..which would operate in two steps...

1. Force the sheet to re-calculate to 100%
2. Paste special values over all ranges which contain formulas...

Let us know..what suits you best...can help out..!

regards,

asingh

WinteE
04-15-2007, 02:38 AM
All cells containing values can be selected in one time :

Edit, Goto, Special, Formulas

asingh
04-15-2007, 03:04 AM
Yups they can be...using the mouse or keyboard cursor..and then copying the contents...using control+C....doing a right click..you can paste special values....right on top....of the formulas..making the cell contents static...with the value...

jpolzner
04-15-2007, 08:46 AM
Hi,
If you manually want to do it..than just do a paste - special - values on the cell..after you are satisfied with the solution...If you want to automate..then VBA would be a good way to go about it..which would operate in two steps...

1. Force the sheet to re-calculate to 100%
2. Paste special values over all ranges which contain formulas...

Let us know..what suits you best...can help out..!

regards,

asingh
Mr. Singh -

Thank you for your kind response. Yes, I was hoping some kind of VBA formula would solve this problem. I have about ten employees and they have to document their time throughout the day........I've got the spreadsheet set up to do all that, but, I would lose it all at midnight, each night, without finding a way to remove the formula and save only the value. I understand that it can be done manually, but with ten employees, each recording their work tasks throughout the day and then those dates and times being entered onto another sheet, I would have A LOT of manual deleting of formulas, saving values, etc., etc.

So, yes, I was hoping some type of VBA code that would run automatically (just before midnight each day???) would solve this problem for me.

Thank you for your offer of help. Let me know if this is possible.

I appreciate your assistance.

Sincerely,

James R. Polzner
Cleveland, Ohio
USA

mdmackillop
04-15-2007, 08:57 AM
Hi James,
I'm not clear on the best way to handle this from the info. given. How many cells are affected? Changing from a formula to data may prevent some functioning. Will the workbook always be open at midnight?
Perhaps if you could post a sample containing the relevant cells, we could get the best solution.

mlenau
04-20-2007, 04:53 PM
I have a similar question so I'm gonna piggyback...

I have a host of vlookups, match, and index formulas that I would be able to search on the ones that don't equal an error. Then if the cell doesn't equal an error, replace the formula with the value of the formula.

I have users that don't use excel well...and I want to automate this process so they don't have to copy and paste values. The source data might possibly change and then data they previously had would now reseult in an error.

I have a macro that will do it for an indivdual cell, but I'd like one that runs on the sheet or range, and finds them all automatically. I'd also like to remove the input box, and just have the macro run on a pre defined range, but not sure how....

Sample of what I have:
Sub ValuesOnly_new()
Dim rRange As Range
On Error Resume Next
Set rRange = Application.InputBox(Prompt:="Select the formulas", _
Title:="VALUES ONLY", Type:=8)
With rRange
rRange = Union(.SpecialCells(xlCellTypeFormulas, xlTextValues), _
.SpecialCells(xlCellTypeFormulas, xlLogical), _
.SpecialCells(xlCellTypeFormulas, xlNumbers))
End With
If rRange Is Nothing Then Exit Sub
rRange = rRange.Value
End Sub

I'm so close and yet so far....

Thanks

johnske
04-20-2007, 07:09 PM
Is there a way to have a formula removed from a cell immediately upon the formula placing the result into the cell, yet, still retain the result?...This is worksheet event code, paste it into the code module of the sheet that you want it applied to

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Target.Dependents
.Value = .Value
End With
End Sub

johnske
04-21-2007, 04:44 AM
Oh, by the way, if you've set workbook calculation to 'manual', use
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Target.Dependents
.Calculate
.Value = .Value
End With
End Sub
instead...

mlenau
04-23-2007, 09:15 AM
Not sure if it's working right...it replaced values kinda willy nilly...Maybe you could explain more...but it replaces the values even if the formula results in an error...

Using if(isserror( and it replaces the value with the error value... currently "--"

Sample formula
=IF(ISERROR(INDEX('WIP'!I:I,MATCH('Test'!$H24,'ATD WIP'!$E:$E,FALSE))),"--",(INDEX('WIP'!I:I,MATCH('Test'!$H24,'ATD WIP'!$E:$E,FALSE))))

johnske
04-23-2007, 01:35 PM
The target is the cell where you're making an entry. Dependents are any and all cells that are on the same worksheet as the target that have a formula that gives a result that is dependent on the value entered in the target cell. It doesn't matter if the result is an error, it's still a dependent.

To be more specific and only convert some of the dependents to values you'd need to write code giving the location of the dependents to be converted relative to the target and to cater for errors... well it's best to have code that doesn't error out.

mlenau
04-23-2007, 02:17 PM
Well I appreciate the help, but it's not working right for me...:banghead:

I have some dependents that are a couple formulas deep, and it replaces all the dependents with the very first precedent's value..

The code I have above looks to see if the formula results in an error or not and pastes values if not...I just can't make it work over a range..

Sub ValuesOnly_new()
Dim rRange As Range
On Error Resume Next
Set rRange = Application.InputBox(Prompt:="Select the formulas", _
Title:="VALUES ONLY", Type:=8)
With rRange
rRange = Union(.SpecialCells(xlCellTypeFormulas, xlTextValues), _
.SpecialCells(xlCellTypeFormulas, xlLogical), _
.SpecialCells(xlCellTypeFormulas, xlNumbers))
End With
If rRange Is Nothing Then Exit Sub
rRange = rRange.Value
End Sub