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!

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!

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!

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

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

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!

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 -

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

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!