Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero out the cell macro
If you change the setting to Formulas, then you just reverse the problem and
the numbers won't get changed. Sub Zeros_for_New_Input() Dim rng As Range, cell As Range On Error Resume Next Set rng = Range("Input_area").SpecialCells(xlConstants, _ xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If Set rng = Nothing Set rng = Range("Input_area").SpecialCells(xlFormulas, _ xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If End Sub Regards, Tom Ogilvy "MacroMan" wrote in message ... Your "SpecialCells" setting is set to values, change it to formulas and you should be okay. -----Original Message----- The macro below is one someone in this forum helped me to start. And it works all the time except for the following condition. Apparently a cell can be unlocked, but if there is a numeric formula such as 1 + 2, then this macro refuses to override that formula with the value of zero. However, if I have unlocked the cell, I want the zero value in there when I run this macro. So, how do I need to change this macro to make the work the way I want it? Thanks, Bruce Sub Zeros_for_New_Input() Dim rng As Range, cell As Range On Error Resume Next Set rng = Range("Input_area").SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If End Sub . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Zero out the cell macro
Works like a charm... Thanks
-----Original Message----- If you change the setting to Formulas, then you just reverse the problem and the numbers won't get changed. Sub Zeros_for_New_Input() Dim rng As Range, cell As Range On Error Resume Next Set rng = Range("Input_area").SpecialCells(xlConstants, _ xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If Set rng = Nothing Set rng = Range("Input_area").SpecialCells(xlFormulas, _ xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If End Sub Regards, Tom Ogilvy "MacroMan" wrote in message ... Your "SpecialCells" setting is set to values, change it to formulas and you should be okay. -----Original Message----- The macro below is one someone in this forum helped me to start. And it works all the time except for the following condition. Apparently a cell can be unlocked, but if there is a numeric formula such as 1 + 2, then this macro refuses to override that formula with the value of zero. However, if I have unlocked the cell, I want the zero value in there when I run this macro. So, how do I need to change this macro to make the work the way I want it? Thanks, Bruce Sub Zeros_for_New_Input() Dim rng As Range, cell As Range On Error Resume Next Set rng = Range("Input_area").SpecialCells(xlConstants, xlNumbers) On Error GoTo 0 If Not rng Is Nothing Then For Each cell In rng If cell.Locked = False Then cell.Value = 0 End If Next End If End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to transfer contents of 'Selected' cell to alternate cell. | Excel Worksheet Functions | |||
'IF' Macro to insert cell contents to alternate cell if cell not e | Excel Worksheet Functions | |||
Macro to Insert Current Date into cell - Macro to "Save As" | Excel Worksheet Functions | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |