VBA reset cell value if greater then 0

  • Thread starter Thread starter roniaelm
  • Start date Start date
R

roniaelm

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!
 
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
 
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
 
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
 
ps. This will change all the cells with numbers to 0.

The positives, negatives and 0's.
 
ps.  This will change all the cells with numbers to 0.

The positives, negatives and 0's.
Click to expand...

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!
 
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
 
If you look at the .specialcells suggestion, you'll notice that it only looks at
cells with number constants.
 
If you look at the .specialcells suggestion, you'll notice that it only looks at
cells with number constants.



(e-mail address removed) wrote:
Click to expand...

Thanks everyone for your suggestions!
I got the macro to work!
All your tips were useful!
 
Back
Top