Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
Hi,
I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
Sub ResetAll()
Dim myRng As Range For Each myRng In Sheets("Sheet1").Range("C21:C31") If myRng.Value < "" Then myRng.Value = 0 Next myRng End Sub -- __________________________________ HTH Bob wrote in message ... Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
try
Sub ResetAll() Dim myRng As Range, c As Range Set myRng = Sheets("Sheet1").Range("C21:C31") For Each c In myRng If c.Value 0 Then c.Value = 0 End If Next End Sub Mike " wrote: Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
Record a macro when you select that range
Then Edit|goto (or hit ctrl-g or F5) Click on the Special button Select Constants, but only select Numbers Type 0 and hit ctrl-enter Stop recording the macro You'll end up with code that looks like: Range("c21:c31").Select Selection.SpecialCells(xlCellTypeConstants, 1).Select Selection.FormulaR1C1 = "0" You can change that to: Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 But if there are no number constants in that range, you'll get an error: on error resume next Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 on error goto 0 Adding the worksheet to qualify the range and using a VBA constant to make it easier to read: on error resume next worksheets("Sheet1").Range("c21:c31") _ .SpecialCells(xlCellTypeConstants, xlNumbers).value = 0 on error goto 0 wrote: Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
ps. This will change all the cells with numbers to 0.
The positives, negatives and 0's. Dave Peterson wrote: Record a macro when you select that range Then Edit|goto (or hit ctrl-g or F5) Click on the Special button Select Constants, but only select Numbers Type 0 and hit ctrl-enter Stop recording the macro You'll end up with code that looks like: Range("c21:c31").Select Selection.SpecialCells(xlCellTypeConstants, 1).Select Selection.FormulaR1C1 = "0" You can change that to: Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 But if there are no number constants in that range, you'll get an error: on error resume next Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 on error goto 0 Adding the worksheet to qualify the range and using a VBA constant to make it easier to read: on error resume next worksheets("Sheet1").Range("c21:c31") _ .SpecialCells(xlCellTypeConstants, xlNumbers).value = 0 on error goto 0 wrote: Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
On Jan 17, 12:22*am, Dave Peterson wrote:
ps. *This will change all the cells with numbers to 0. The positives, negatives and 0's. Dave Peterson wrote: Record a macro when you select that range Then Edit|goto (or hit ctrl-g or F5) Click on the Special button Select Constants, but only select Numbers Type 0 and hit ctrl-enter Stop recording the macro You'll end up with code that looks like: * * Range("c21:c31").Select * * Selection.SpecialCells(xlCellTypeConstants, 1).Select * * Selection.FormulaR1C1 = "0" You can change that to: * * Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 But if there are no number constants in that range, you'll get an error: * * on error resume next * * Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 * * on error goto 0 Adding the worksheet to qualify the range and using a VBA constant to make it easier to read: * * on error resume next * * worksheets("Sheet1").Range("c21:c31") _ * * * * * .SpecialCells(xlCellTypeConstants, xlNumbers).value = 0 * * on error goto 0 wrote: Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! -- Dave Peterson -- Dave Peterson Thanks everyone soo much for your help! It's amazing what Excel can do! I used Bob's one and it worked well. I wonder as an added feature needed is it possible to also change the macro that it does not delete formulas if there are formulas in the column? I just want it to reset cell values that are greater then 0 and not over-write formulas? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
Hi
This modification of Bob's code should do it: Sub ResetAll() Dim myRng As Range For Each myRng In Sheets("Sheet1").Range("C21:C31") If myRng.HasFormula = False Then If myRng.Value < "" Then myRng.Value = 0 End If Next myRng End Sub Regards, Per On 17 Jan., 02:55, wrote: On Jan 17, 12:22*am, Dave Peterson wrote: ps. *This will change all the cells with numbers to 0. The positives, negatives and 0's. Dave Peterson wrote: Record a macro when you select that range Then Edit|goto (or hit ctrl-g or F5) Click on the Special button Select Constants, but only select Numbers Type 0 and hit ctrl-enter Stop recording the macro You'll end up with code that looks like: * * Range("c21:c31").Select * * Selection.SpecialCells(xlCellTypeConstants, 1).Select * * Selection.FormulaR1C1 = "0" You can change that to: * * Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 But if there are no number constants in that range, you'll get an error: * * on error resume next * * Range("c21:c31").SpecialCells(xlCellTypeConstants, 1).value = 0 * * on error goto 0 Adding the worksheet to qualify the range and using a VBA constant to make it easier to read: * * on error resume next * * worksheets("Sheet1").Range("c21:c31") _ * * * * * .SpecialCells(xlCellTypeConstants, xlNumbers).value = 0 * * on error goto 0 wrote: Hi, I have this macro to reset cells in C21-C31 to 0. However, how do I amend this macro that it will only re-set the cells to 0 if it the cell contains a value greater then 0 as I do not wish for the macro to add a 0 to cells that are empty with no cell values in them at all. Sub ResetAll() Dim myRng As Range Set myRng = Sheets("Sheet1").Range("C21:C31") myRng.Value = "0" End Sub Is it possible to do this? Thanks! -- Dave Peterson -- Dave Peterson Thanks everyone soo much for your help! It's amazing what Excel can do! I used Bob's one and it worked well. I wonder as an added feature needed is it possible to also change the macro that it does not delete formulas if there are formulas in the column? I just want it to reset cell values that are greater then 0 and not over-write formulas? Thanks!- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
If you look at the .specialcells suggestion, you'll notice that it only looks at
cells with number constants. wrote: <<snipped Thanks everyone soo much for your help! It's amazing what Excel can do! I used Bob's one and it worked well. I wonder as an added feature needed is it possible to also change the macro that it does not delete formulas if there are formulas in the column? I just want it to reset cell values that are greater then 0 and not over-write formulas? Thanks! -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA reset cell value if greater then 0
On Jan 17, 1:42*pm, Dave Peterson wrote:
If you look at the .specialcells suggestion, you'll notice that it only looks at cells with number constants. wrote: <<snipped Thanks everyone soo much for your help! It's amazing what Excel can do! I used Bob's one and it worked well. I wonder as an added feature needed is it possible to also change the macro that it does not delete formulas if there are formulas in the column? I just want it to reset cell values that are greater then 0 and not over-write formulas? Thanks! -- Dave Peterson Thanks everyone for your suggestions! I got the macro to work! All your tips were useful! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reset a cell | Excel Programming | |||
Set cell to record date when adjacent cell is filled AND NOT RESET | Excel Worksheet Functions | |||
how do you reset your arrow keys to move cell to cell? | New Users to Excel | |||
Addition to Turn cell red if today is greater or equal to date in cell | New Users to Excel | |||
Turn cell red if today is greater or equal to date in cell | New Users to Excel |