If Error Then Blank

Question

Thanks for taking a look at my question. Know that I am new to stackoverflow and VBA I have a long macro I've been working on. At one point in the macro I have to convert "Start Depth" (L) and "End Depth" (M) from "m" to "ft". Then I subtract the two to find the "Footage" (N). However, some of the values in the columns are originally left blank. So, after making my conversions and subtractions, I'm left with "#VALUE!" which is giving me errors later on in the macro. Originally I had changed all of the blanks to 0's before the conversions and subtractions. But, After "finishing" the macro I realize the zeros are messing with presenting the data. So, I'd like to just do the conversions and subtractions and then, change all the "#VALUES!" back to blanks. I found some stuff on this but nothing that I could (that i know of) use or specific to me: http://www.ozgrid.com/forum/showthread.php?t=60740 and https://superuser.com/questions/715744/excel-2010-formula-how-do-i-write-this-formula-vba

Here is what i was using to change blanks into 0's

Worksheet1.Select
lastrow = Range("A666666").End(xlUp).Row
For Each cell In Range("A1:Q" & lastrow)
    If Len(cell.Value) = 0 Then
        cell.Value = 0
    End If
Next

Here is the code resulting in errors. Note: The data starts with blanks and after using these formulas I am given errors because some of the original cells begin as null or blanks. Also, These lines aren't in this order but, they are the lines leaving errors.

ActiveCell.FormulaR1C1 = "=CONVERT(RC[2],""m"",""ft"")"
Selection.AutoFill Destination:=Range("N2:O2"), Type:=xlFillDefault
Selection.AutoFill Destination:=Range("N2:O" & lastrow)
Range("N1") = "Footage"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.AutoFill Destination:=Range("N2:N" & lastrow)

Any help is appreciated. Thanks guys!

Answer 1

It depends if the #VALUE! is being generated from a formula or from VBA code.

Formula

If it's being generated from a formula, wrap the formula in the IFERROR function. This was added in Excel 2007 I believe. So for example if your formula was:

=A1-B1

Then you could put

=IFERROR(A1-B1,"")

Which is saying, if A1-B1 is an error, return "", otherwise return the result of A1-B1.

VBA

If the value is being generated by VBA you could write a helper function that works like IFERROR

Public Function MyIfError(value As Variant, value_if_error As Variant)
 If IsError(value) Then
    MyIfError = value_if_error
 Else
   MyIfError = value
 End If
End Function

And then pass your value through that.

Answer 2

Worksheet1.Select
lastrow = Range("A666666").End(xlUp).Row
For Each cell In Range("A1:Q" & lastrow)
    If Len(cell.Value) = 0 Then
        cell.Value = 0
    End If
Next

For Each cell In Range("A1:Q" & lastrow)
    cell.value = WorksheetFunction.Iferror(cell.value,"")
    End If
Next

I'm sorry I don't have opprtunity to test it right but give it a try. Bottom line is that I simply included a second loop that runs IFERROR function on your data.

Answer 3

This does not directly answer your question, but it does fix the presentation of Zero's on your worksheet. You can use the following setting to show Zeros as Blanks through VBA: ActiveWindow.DisplayZeros = False It might be a consideration instead of looping through everything looking for 0's and switching them to blanks manually.