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
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
Something like this maybe? Just a shell. Doesn't address the space issue or the specified range you posted.
[vba]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[/vba]
Peace of mind is found in some of the strangest places.
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
ok maybe this
[VBA]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[/VBA]
Peace of mind is found in some of the strangest places.
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.
hmmm.. Dont have time to debug it right now. Im sure someone else will come along and fix it though.
Peace of mind is found in some of the strangest places.
Looks good to me Austen. I think we need to see a sample to debug.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Could be the operator ;-)
ill post a sample tommorow..thanks much
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
[vba]
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
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
No, it should be automatic because they are formulae which woud be recalculated.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
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
The problem is that the two formulae in E17, E18 are array formulae. If you edit and array-enter them, all will be well.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Thanks XID