PDA

View Full Version : Solved: Cell value = #NUM!



Klartigue
08-10-2012, 02:52 PM
When I have a formula and it can't calculate, the value left in the cell is "#NUM!" How can I say that if the cell value is "#NUM!", then put the number "0" in that cell.

I tried this but it doesn't work:


Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If cll.Value = "#NUM!" Then cll.Value = "0"
Next cll

End Sub

CatDaddy
08-10-2012, 03:13 PM
Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
cll.value = cll.value
If cll.Value = "#NUM!" Then
cll.Value = "0"
End if
Next cll

End Sub

CatDaddy
08-10-2012, 03:14 PM
or better yet make the original formula you used:

=IFERROR(yourformulafirst(), "0")

then if it hits an error instead of putting the error message in the cell it will put a "0"

mikerickson
08-10-2012, 10:17 PM
Along the same lines, what is this function that returns #NUM?
Perhaps a different formula will get the same result without errors?

Bob Phillips
08-12-2012, 03:32 AM
Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If cll.Text = "#NUM!" Then cll.Value = "0"
Next cll
End Sub

Klartigue
08-13-2012, 06:15 AM
I tried this expression, but it said there was an error: Type mismatch.

Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
cll.Value = cll.Value
If cll.Value = "#NUM!" Then
cll.Value = "0"
End If
Next cll

End Sub

Klartigue
08-13-2012, 06:16 AM
This formula works!!!

Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If cll.Text = "#NUM!" Then cll.Value = "0"
Next cll
End Sub

Bob Phillips
08-13-2012, 06:56 AM
The cell value is not #NUM, it is actually Error 2036. #NUM that is just what you see, that is why Itested the Text property not the Value property.

You could actually test the value property like so

Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If cll.Value = CVErr(xlErrNum) Then cll.Value = 0
Next cll
End Sub

Klartigue
08-13-2012, 07:01 AM
I tried this code but it says there is a type mismatch

Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If cll.Value = CVErr(xlErrNum) Then cll.Value = 0
Next cll
End Sub

Bob Phillips
08-13-2012, 07:04 AM
Sorry about that, I was forgetting they wouldn't all be errors :)

It should be

Sub ZerosAndBlanksCash()

Dim cll As Range, rng As Range
Set rng = Range("I2:R" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each cll In rng
If IsError(cll.Value) Then

If cll.Value = CVErr(xlErrNum) Then cll.Value = 0
End If
Next cll
End Sub

Klartigue
08-13-2012, 09:16 AM
Great, that works! Thank you!