Clear values, save formulas; Recalculate

**itsunclebill** · 03-20-2010, 02:21 PM

The attached sheet is used to inventory lottery tickets. The ticket number is scanned with a barcode reader and the info placed on an input line. Several other details are added then the line is "Added to inventory" with the macro button. The macro clears the input line so the next item can be entered. I can't clear the values without dumping the formulas in the calculating cells, which wouldn't be an issue but the cells won't recalculate the new input.

I need to be able to clear the cell values (but leave the formulas) and need to kick the sheet to recalc the input row. Ideas?

**royUK** · 03-20-2010, 02:31 PM

Not sure what you mean, the code clears the cells,but leaves the formulas in the input line

**itsunclebill** · 03-20-2010, 02:41 PM

The macro runs this

Code:

For Each cell In Range("rgnINV")
        If Not cell.HasFormula Then cell.ClearContents
    Next cell

as part of the process. It's the only way I know to clear the cells with values in them. The code leaves formulas but also leaves the previously calc'd value. The line doesn't want to recalculate so I have to manually force the calculations to happen and don't understand why.

Hit the "add to Inventory" key and see what happens

**royUK** · 03-20-2010, 03:15 PM

Your example has Calculation set to Manual, see the Formulas Tab

**itsunclebill** · 03-20-2010, 08:46 PM

Didn't know THAT was even in there.

Thanks.

**royUK** · 03-21-2010, 05:20 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**royUK** · 03-21-2010, 05:31 AM

This will be quicker than your loop for non formula cells

Code:

    On Error Resume Next
    Range("rgnINV").SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo 0

Also,why the macro Sheet?

**itsunclebill** · 03-21-2010, 07:23 PM

Thanks for the clearcontents.

Macro sheet is junk left over from another project and is just there and handy for the data valadation list. The project is VERY early alnog at this point and I haven't done my housecleaning yet.