ISERROR VBA CODE

[gimli]

Hi,

I have a bunch of cells I would like to use ISERROR for...can it be coded in VBA to handle a range of cells and return a value?

EX J8:Q17

On error return a space..

thanks
much

[austenr]

Something like this maybe? Just a shell. Doesn't address the space issue or the specified range you posted.


Sub ErrorFix()
Dim cell As Range
Dim orig As String
For Each cell In ActiveSheet.UsedRange
If IsError(cell) Then
orig = Mid(cell.Formula, 2, Len(cell.Formula) - 1)
cell.Formula = "=IF(ISERROR(" & orig & "),0," & orig & ")"
End If
Next cell
End Sub

[gimli]

Yeah im pretty noob to vba..so that just flew past me..ha

Im guessing that would apply to the whole sheet? Specifying a range would be great if can suggest something

[austenr]

ok maybe this


Sub ErrorFix()
Dim myRange As Range
Dim cell As Range
Dim orig As String
Set myRange = Range("B10:B25")
For Each cell In myRange
If IsError(cell) Then
orig = Mid(cell.Formula, 2, Len(cell.Formula) - 1)
cell.Formula = "=IF(ISERROR(" & orig & "),0," & orig & ")"
End If
Next cell
End Sub

[gimli]

Ok..I put this in the workbook module. Seems to work too good. Even when there is no #REF! error and a good value should populate the cell, it puts a space.

[austenr]

hmmm.. Dont have time to debug it right now. Im sure someone else will come along and fix it though.

[mdmackillop]

Looks good to me Austen. I think we need to see a sample to debug.

[gimli]

Could be the operator ;-)

ill post a sample tommorow..thanks much

[gimli]

Hey all,

here is a file that will help show my question. I basically want to remove the ref errors and keep the zero values in the cell but I would not like the zero values to show. I know I can probably use conditional formatting but if someone could suggest some vba that would be great.

If you dont mind converting cell formulas g17 and j17 to vba I can build off them for other cells in my workbook.

thanks much

[Bob Phillips]

Sub ErrorFix()
Dim cell As Range
Dim orig As String
For Each cell In Range("G17:N17")
If IsError(cell.Value2) Then
orig = Right$(cell.Formula, Len(cell.Formula) - 1)
cell.Formula = "=IF(ISERROR(" & orig & "),""""," & orig & ")"
End If
Next cell
ActiveWindow.DisplayZeros = False
End Sub

[gimli]

XID,

Works great. I run sub 1 time and it clears all the errors...perfect.

but when the values in the cells change to good data, the good data should show but the blanks stay. Do I need to make a sub to populate the cells, the run the error sub, then populate again?

sorry for so many questions..im just not getting it

[Bob Phillips]

No, it should be automatic because they are formulae which woud be recalculated.

[gimli]

XID,

I attached a file. As the values change in P30:P39 the values in E14:L18 change. Your code will remove the errors but when the values in P30:P39 are changed again it doesnt seem to work.

thanks much

[Bob Phillips]

The problem is that the two formulae in E17, E18 are array formulae. If you edit and array-enter them, all will be well.

[gimli]

Thanks XID