Changing Values In A Cell

R

RAYMOND KELLY

Is there an easy way to delete the contents of a cell when that cell is not null and not greater than 0?
if the cell is a number greater than 0, I want it to remain the same.
If it is anything but a number greater than 0, I want to make it null.

Thanks in advance.
 
B

Bernie Deitrick

Raymond,

For cell A1:

If Not (IsNumeric(Range("A1").Value) And (Range("A1").Value > 0)) Then
Range("A1").ClearContents

As a Sub:

Sub myCellCheck(inCell As Range)
If Not (IsNumeric(inCell.Value) And (inCell.Value > 0)) Then
inCell.ClearContents
End Sub

Called like:
Sub test()
myCellCheck Range("A1")
End Sub

HTH,
Bernie
MS Excel MVP

Is there an easy way to delete the contents of a cell when that cell is not
null and not greater than 0?
if the cell is a number greater than 0, I want it to remain the same.
If it is anything but a number greater than 0, I want to make it null.

Thanks in advance.
 
R

RAYMOND KELLY

Bernie,

Thank you.

Will this work when it is more than one cell?
I need to run the code once for all cells in the range A1..C100.
 
B

Bernie Deitrick

Raymond,

Dim inCell As Range

For Each inCell In Range("A1:C100")
If Not (IsNumeric(inCell.Value) And (inCell.Value > 0)) Then
inCell.ClearContents
Next inCell

HTH,
Bernie
MS Excel MVP

RAYMOND KELLY said:
Bernie,

Thank you.

Will this work when it is more than one cell?
I need to run the code once for all cells in the range A1..C100.
Click to expand...
 
B

Bernie Deitrick

Raymond,

That code line- wrapped at just the wrong place...Here's one that will work
with the wrapping.

Dim inCell As Range

For Each inCell In Range("A1:C100")
If Not (IsNumeric(inCell.Value) And (inCell.Value > 0)) Then
inCell.ClearContents
End If
Next inCell


HTH,
Bernie
MS Excel MVP

Bernie Deitrick said:
Raymond,

Dim inCell As Range

For Each inCell In Range("A1:C100")
If Not (IsNumeric(inCell.Value) And (inCell.Value > 0)) Then
inCell.ClearContents
Next inCell

HTH,
Bernie
MS Excel MVP
Click to expand...
 
R

RAYMOND KELLY

Bernie,

I added the End If in the code from the prior e-mail and it worked like a
charm.

Thanks for your help.
Raymond
 
Top