Solved: Delete Any Numbers that are less than 1

[Djblois]

I want to be able to delete any numbers in a column that are fractions less than the #1. Is this possible?

Along the same lines I want to be able to delete all the numbers in a column that are less than 1 cent different than the line above it. Also is this possible?

[Djblois]

I think I figured them out. If I am right then I am stupid for having to ask.

[Djblois]

If Cells(i, "C") = Cells(i + 1, "C").Value Then
If Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7" < 1 Then
Range("K" & i).FormulaR1C1 = ""
Else
Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7"
End If
End If

This is the code I am using. However it is deleting all of range k. Well it isn't deleting K but it isn't calculating any of them.

[Djblois]

VBA:
If Cells(i, "C") = Cells(i + 1, "C").Value Then If Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7" < 1 Then Range("K" & i).FormulaR1C1 = "" Else Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7" End If End If

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com/)


This is the code I am using. However it is deleting all of range k

[lucas]

Threads merged.....????

[Norie]

I'm sorry but your comparison here makes no sense.

If Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7" < 1 Then

This will not look at the value in the cell.

First it'll see if the formula in the cell is =(RC[-5]-R[1]C[-5])/7.

If it is it'll return TRUE, which it will check to see if it's less than one.

I'm not sure exactly how VBA will handle that.

[Djblois]

ok that explains why it doesn't work but now how do I use that to figure out if that formula is less than 1 then do this?

[Djblois]

ok this is what I got working:

Range("K" & i).FormulaR1C1 = "=(RC[-5]-R[1]C[-5])/7"
If Range("K" & i) < 1 Then
Range("K" & i).ClearContents
End If

[Djblois]

Now I am going to try and work on the second part

[Djblois]

THis is what I have for the second part. However, I just figured out I need to clear the contents of any that are between 1 cent and -1 cent. How do I do that?

Range("J" & i).FormulaR1C1 = "=RC[-2]-R[1]C[-2]"
If Range("J" & i) < 0.01 Then
Range("J" & i).ClearContents
End If

[Djblois]

This is what I am using for the second part:

If Range("J" & i) < 0.01 & Range("J" & i) > -0.01 Then
Range("J" & i).ClearContents
End If

And it isn't working

[Bob Phillips]

You should be comparing both (AND) not concatenating them (&)



If Range("J" & i) < 0.01 AND Range("J" & i) > -0.01 Then
Range("J" & i).ClearContents
End If


But you can do without either





If Abs(Range("J" & i).Value) < 0.01 Then
Range("J" & i).ClearContents
End If

[Djblois]

Thank you that worked