Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Can anyone throw me a bone here.
I need to write some code to attache to a command button that wil check an entire spreadsheet and clear all numeric data. Basically th spreadsheet is for cost analysis and we enter hours. I want to clic the button and have it clear all the hours from all cells and ignor any text data. Any help would be greatly appreciated. Thanks -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
On error Resume next
Activesheet.Cells.Specialcells(xlConstants,xlNumbe rs).Clearcontents On Error goto 0 -- Regards, Tom Ogilvy "chryo " wrote in message ... Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
One way:
Public Sub ClearNumbers() On Error Resume Next Cells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents On Error GoTo 0 End Sub In article , chryo wrote: Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
How about recording a macro when you select your range.
then edit|goto special check Constants and just numbers then click ok then hit the delete key (to clear contents) I got this when I did it: Range("A7:E20").Select Selection.SpecialCells(xlCellTypeConstants, 1).Select Selection.ClearContents You could modify it to: on error resume next Range("A7:E20").SpecialCells(xlCellTypeConstants, 1).ClearContents on error goto 0 (on error resume next means that the code won't blow up if there are no constant numbers.) (And I'd try to limit my range--there may be some stuff you want to keep--like dates/headers.) "chryo <" wrote: Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Chryo, there is a problem with the specialcells approach in the prior
suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an input cell. The specialcells would only pick up entries like 33, 44, etc, that are not entered with an equal sign. The only approach I have found to do what you want is to check numeric cells cell by cell and if they do not contain any letters, then they are input cells. The routine below is an approach on how to do. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel Sub ClearNumbers() Dim numCells As Range Dim formulaCells As Range Dim cellsToClear As Range On Error Resume Next 'get numeric cells without equal sign at start Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers) 'get all cells with an equal sign at start Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0 'exit if no matching cells If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub 'concationate the two selections into one If numCells Is Nothing Then Set inputcells = formulaCells ElseIf formulaCells Is Nothing Then Set inputcells = formulaCells Else Set inputcells = Union(formulaCells, numCells) End If 'check for entries w/o a, b, c... which would be a non 'input cell For Each cell In inputcells If bNumber(cell.Formula) Then If cellsToClear Is Nothing Then Set cellsToClear = cell Else Set cellsToClear = Union(cell, cellsToClear) End If End If Next 'if no input cells, exit If cellsToClear Is Nothing Then Exit Sub 'set input cells to zero cellsToClear.Value = 0 End Sub Private Function bNumber(cellFormula As String) As Boolean 'return false if cells contain a letter Dim I As Integer cellFormula = UCase(cellFormula) For I = 65 To 90 If InStr(cellFormula, Chr(I)) 0 Then Exit Function Next bNumber = True End Function "chryo " wrote in message ... Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Just some observations in addition to your own:
your bNumber function is a waste of time since formulaCells and numCells only include cells that would pass the IsNumber test. Also, there are many other characters besides upper or lowercase characters that would make the cell non numeric - so the check is flawed as well. In any event, it appears unlikely that the user, if entering time values, is entering things like =0.6532134576 -- Regards, Tom Ogilvy "Bob Flanagan" wrote in message ... Chryo, there is a problem with the specialcells approach in the prior suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an input cell. The specialcells would only pick up entries like 33, 44, etc, that are not entered with an equal sign. The only approach I have found to do what you want is to check numeric cells cell by cell and if they do not contain any letters, then they are input cells. The routine below is an approach on how to do. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel Sub ClearNumbers() Dim numCells As Range Dim formulaCells As Range Dim cellsToClear As Range On Error Resume Next 'get numeric cells without equal sign at start Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers) 'get all cells with an equal sign at start Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0 'exit if no matching cells If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub 'concationate the two selections into one If numCells Is Nothing Then Set inputcells = formulaCells ElseIf formulaCells Is Nothing Then Set inputcells = formulaCells Else Set inputcells = Union(formulaCells, numCells) End If 'check for entries w/o a, b, c... which would be a non 'input cell For Each cell In inputcells If bNumber(cell.Formula) Then If cellsToClear Is Nothing Then Set cellsToClear = cell Else Set cellsToClear = Union(cell, cellsToClear) End If End If Next 'if no input cells, exit If cellsToClear Is Nothing Then Exit Sub 'set input cells to zero cellsToClear.Value = 0 End Sub Private Function bNumber(cellFormula As String) As Boolean 'return false if cells contain a letter Dim I As Integer cellFormula = UCase(cellFormula) For I = 65 To 90 If InStr(cellFormula, Chr(I)) 0 Then Exit Function Next bNumber = True End Function "chryo " wrote in message ... Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Tom, the use of special cells at the start selects only the numeric cells.
The intention of the bNumber function is to exclude from those numeric cells the cells that are true formulas, such as =A1+B1 and return true when there is no letter and thus a true number input. Thus it is not flawed or a waste of time. The intent is not to refilter to exclude numeric cells. Characters that change a number to a non numeric are not relevant and filtered out at the beginning by the use of specialcells. Lastly, the user said he was entering hours, not time. An hour entry can be a number like 3, or an entry =4 + 45/60. How would you suggest he identify and clear entries like = 4 + 45/60 ? Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "Tom Ogilvy" wrote in message ... Just some observations in addition to your own: your bNumber function is a waste of time since formulaCells and numCells only include cells that would pass the IsNumber test. Also, there are many other characters besides upper or lowercase characters that would make the cell non numeric - so the check is flawed as well. In any event, it appears unlikely that the user, if entering time values, is entering things like =0.6532134576 -- Regards, Tom Ogilvy "Bob Flanagan" wrote in message ... Chryo, there is a problem with the specialcells approach in the prior suggestions. A cell with a formula like = 4 or =5+9 is not picked up as an input cell. The specialcells would only pick up entries like 33, 44, etc, that are not entered with an equal sign. The only approach I have found to do what you want is to check numeric cells cell by cell and if they do not contain any letters, then they are input cells. The routine below is an approach on how to do. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel Sub ClearNumbers() Dim numCells As Range Dim formulaCells As Range Dim cellsToClear As Range On Error Resume Next 'get numeric cells without equal sign at start Set numCells = ActiveSheet.Cells.SpecialCells(xlConstants, xlNumbers) 'get all cells with an equal sign at start Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) On Error GoTo 0 'exit if no matching cells If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub 'concationate the two selections into one If numCells Is Nothing Then Set inputcells = formulaCells ElseIf formulaCells Is Nothing Then Set inputcells = formulaCells Else Set inputcells = Union(formulaCells, numCells) End If 'check for entries w/o a, b, c... which would be a non 'input cell For Each cell In inputcells If bNumber(cell.Formula) Then If cellsToClear Is Nothing Then Set cellsToClear = cell Else Set cellsToClear = Union(cell, cellsToClear) End If End If Next 'if no input cells, exit If cellsToClear Is Nothing Then Exit Sub 'set input cells to zero cellsToClear.Value = 0 End Sub Private Function bNumber(cellFormula As String) As Boolean 'return false if cells contain a letter Dim I As Integer cellFormula = UCase(cellFormula) For I = 65 To 90 If InStr(cellFormula, Chr(I)) 0 Then Exit Function Next bNumber = True End Function "chryo " wrote in message ... Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Note that you could eliminate the whole expensive bNumber looping
routine by replacing if bNumber(cell.Formula) Then with if Not cell.Formula Like "*[A-Za-z]*" Then and a real niggle: If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub 'concationate the two selections into one If numCells Is Nothing Then Set inputcells = formulaCells ElseIf formulaCells Is Nothing Then Set inputcells = formulaCells Else Set inputcells = Union(formulaCells, numCells) End If would be a bit more efficient as If numCells Is Nothing Then If formulaCells Is Nothing Then Exit Sub Else Set inputcells = formulaCells End If Elseif formulaCells Is Nothing Then Set inputcells = numCells Else Set inputcells = Union(numCells, formulaCells) End If Since numCells and formulaCells have to be evaluated exactly once in each branch, as opposed to at least one of them, and possibly both, evaluated twice. Note also that this is a bit misleading, though irrelevant to the code: 'get all cells with an equal sign at start Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) The code actually gets only cells with numeric formulae, not "all cells with an equal sign at start". In article , "Bob Flanagan" wrote: Chryo, there is a problem with the specialcells approach in the prior suggestions. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Nice upgrades!
Bob "JE McGimpsey" wrote in message ... Note that you could eliminate the whole expensive bNumber looping routine by replacing if bNumber(cell.Formula) Then with if Not cell.Formula Like "*[A-Za-z]*" Then and a real niggle: If numCells Is Nothing And formulaCells Is Nothing Then Exit Sub 'concationate the two selections into one If numCells Is Nothing Then Set inputcells = formulaCells ElseIf formulaCells Is Nothing Then Set inputcells = formulaCells Else Set inputcells = Union(formulaCells, numCells) End If would be a bit more efficient as If numCells Is Nothing Then If formulaCells Is Nothing Then Exit Sub Else Set inputcells = formulaCells End If Elseif formulaCells Is Nothing Then Set inputcells = numCells Else Set inputcells = Union(numCells, formulaCells) End If Since numCells and formulaCells have to be evaluated exactly once in each branch, as opposed to at least one of them, and possibly both, evaluated twice. Note also that this is a bit misleading, though irrelevant to the code: 'get all cells with an equal sign at start Set formulaCells = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1) The code actually gets only cells with numeric formulae, not "all cells with an equal sign at start". In article , "Bob Flanagan" wrote: Chryo, there is a problem with the specialcells approach in the prior suggestions. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code Assistance
Another option if you're worried about the user typing formulas into the input
cells. Select all the cells that can be used for data entry and give it a nice range name (Insert|name|define). Then just: worksheets("inputsheetname").range("inputrng1").cl earcontents Then you won't have to worry about how the user types his/her entries. "chryo <" wrote: Can anyone throw me a bone here. I need to write some code to attache to a command button that will check an entire spreadsheet and clear all numeric data. Basically the spreadsheet is for cost analysis and we enter hours. I want to click the button and have it clear all the hours from all cells and ignore any text data. Any help would be greatly appreciated. Thanks! --- Message posted from http://www.ExcelForum.com/ -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Assistance | Excel Discussion (Misc queries) | |||
CODE Assistance needed PLEASE please please | Excel Discussion (Misc queries) | |||
Need some assistance | Excel Worksheet Functions | |||
Need some assistance | Charts and Charting in Excel | |||
Assistance with code Please | Excel Discussion (Misc queries) |