PDA

View Full Version : Solved: ISERROR VBA CODE



gimli
04-20-2010, 09:36 AM
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
04-20-2010, 09:53 AM
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
04-20-2010, 10:06 AM
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
04-20-2010, 10:16 AM
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
04-20-2010, 10:50 AM
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
04-20-2010, 12:10 PM
hmmm.. Dont have time to debug it right now. Im sure someone else will come along and fix it though.

mdmackillop
04-20-2010, 03:13 PM
Looks good to me Austen. I think we need to see a sample to debug.

gimli
04-20-2010, 03:47 PM
Could be the operator ;-)

ill post a sample tommorow..thanks much

gimli
04-28-2010, 05:50 AM
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
04-28-2010, 06:47 AM
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
04-28-2010, 08:39 AM
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
04-28-2010, 09:40 AM
No, it should be automatic because they are formulae which woud be recalculated.

gimli
04-28-2010, 09:46 AM
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
04-28-2010, 01:09 PM
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
04-29-2010, 05:02 AM
Thanks XID