Software

Ask something
Discussion Locked

Excel 2000 # value

0 helpful
3 replies

by ped 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a timecard tracking spreadsheet that I use for work that consolidates 5 employee timecards onto a department timecard.

The formulas are all good and the timecard works great, except that each week the employees delete the data from the last week and this causes me to have to review the consolation spreadsheet for #value errors. I have to go to each spreadsheet and hit the delete key in order to clear the # value error on the topsheet.

Is there a macro that I can build and run to clear all non-numeric values for the 5-6 data ranges on each of the timecards?

3 replies

by dklippert

Here are a couple of ways to solve the problem.
One: change the formulas so that #Value does nor occur -- use Sum instead of A1+B1+C1.
Two: prevent the error from being displayed -- =IF(ISERROR(A7+A8), "Need Data",A7+A8)
Three: Heres the macro that selects Text including spaces but not formulas.

Sub SelectText()
Selection.SpecialCells(xlCellTypeConstants, 2).Select
End Sub
(There are only three lines in the macro)
You can duplicate it by going to Edit>Go To and click Special. Choose Constants and deselect all formula options except text.
Good luck

This discussion is closed to new replies.