Find Method

[Justinlabenne]

I have attached a workbook example for people to test out, and see if they could improve on a code I wrote to mask formula errors. I use "ISERROR" alot, especially when users at work dont print reports, they view on the pc. It just seems to be a ? that I always see people asking for, even if it means masking syntax errors and otherwise.

So I came up with some versions of a code to update any formula that returns an error. It uses a For > Next loop, and works so far as I can tell, but if for some reason there are huge amounts of formulas I need a faster method.

I was not sure how to come up with a method using "FIND" so, if anyone can improve on this in any way, speed wise, please let me know.

2 codes in the example have timers, and it goes through 12 columns and 1000 rows of errors. Happy to hear your feedback, and if your wondering why I went with a certain method or something, its probably because I havent any idea how to do it diffrent. I picked this apart for 2 weeks do far, so it all i can give....


Justin

[Jacob Hilderbrand]

Are the formulas for each Column the same? I mean for Column A are the formulas all the same with only the references changing? In the example you have:

A1 = N1/O1
A2 = N2/O2
A3 = N3/O3
A4 = N4/O4

Is that the case with your actual data? If it is, then we can just change the first row and use Auto Fill to update the rest much faster than a loop.

[Jacob Hilderbrand]

Using Auto Fill take 120 milliseconds for me.


Option Explicit

Sub Macro1()
Dim R As Range
Dim MyRange As Range
Dim b As Integer
Dim c As String
Dim x As Long
Dim LastRow As Long
StartTime
LastRow = Range("A65536").End(xlUp).Row
Set MyRange = Range("A1:L1")
For Each R In MyRange
x = 1
b = Len(R.Formula)
c = Right(R.Formula, b - x)
R.Formula = "=IF(ISERROR(" & c & "),"""",(" & c & "))"
Next R
MyRange.AutoFill Destination:=Range("A1:L" & LastRow)
StopTime
MsgBox "How fast!!: " & TimeOnStop & " milliseconds"

End Sub

[Justinlabenne]

My bad, this wb was just an example of random no point formulas, i just posted this to see what other people thought, and to find if there are any other methods, like yuo suggested.

My problem arose from having random formlas all over a sheet that I could not just use autofill for, and I saw someone post this kind of ? on the MrExcel forum, He had all kinds of random formulas all over his spreadsheet that he wished he could just run a code for to add an ISERROR or ISNA to.

I know the FIND method is way faster than running a FOR NEXT loop, how can I use FIND to check cells that are returning errors? I dont really have this problem per se, I just had 2 weeks off of work, and too much time on my hands.

[Jacob Hilderbrand]

We can narrow the range first. Then run a loop on only those cells that are errors.


Set MyRange = Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeFormulas, 16)

The run a For Each loop on MyRange.

[Justinlabenne]

Thanks Jake, appreciate you input as always. I was on Mark 007's site and just happened to Download that example FIND wb. That gave me the idea of trying to speed this puppy up. Just have no idea how to check cells for errors using that method. If you think of a way, or know one I would be interested in to know how it is done. I will definetly change the range to UsedRange, much better idea, glad I posted. Again, thanks for your knowledge Jake. Appreciated..

[Jacob Hilderbrand]

Note: I am not changing the range to UsedRange. I am changing it to all cells in the UsedRange that are errors. Only cells with errors are in this range and you can then loop through just the cells you wanted in the first place.

[Richie(UK)]

Hi Justin,

In case you are interested, I've attached an example workbook that I prepared a while ago. The code enables you to choose which type of error that you want to convert (just in case there are some that you wish to keep! ;) )

The code uses the approach suggested above by Jacob, ie use the SpecialCells method to narrow the range of cells being processed and then enclose the formula in an IsError construct.

Keep in mind that this is really just a 'quick fix'. You may be better off looking at why the errors arise in the first place.

HTH