If #REF! is found then Error Box Macro

Question

I am trying to complete a simple macro that looks for '#REF!' in a worksheet due to a user alterting a row and ruining underlying formulas.

I have got as far as the find:

Sheets("Location_Multiple").Select
Range("A1:AL10000").Select

Selection.Find(What:="#REF!", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

From what I understand I need to enter an If parameter is true then

MsgBox"Please go back and check...."

Im just not sure what should follow the if....

Any pointers would be very much appreciated.

see [this](http://vba4all.wordpress.com/category/vba-macros/highlight-cells-based-on-a-condition/) to get started. In `If InStr(1, cell, searchTerm, vbTextCompare) Then ` you need to change `cell` to `cell.text` and it should work — , Sep 26 '13 at 09:47

score 5 · Accepted Answer · answered Sep 26 '13 at 09:49

5

Try below code

Sub DisplayError()

On Error Resume Next
Dim rng As Range
Set rng = Sheets("Location_Multiple").Range("A1:AL10000")

Dim rngError As Range
Set rngError = rng.SpecialCells(xlCellTypeFormulas, xlErrors)

If Not rngError Is Nothing Then
    For Each cell In rngError
        MsgBox "Please go back and check.... " & cell.Address
    Next
End If
End Sub

answered Sep 26 '13 at 09:49

Santosh

12,175
4
41
72

Perfect..worked a treat,, sorry to ask another question still being a VBA newbie, what is the clasue to show a box if there is no error? – Methexis Sep 26 '13 at 15:14

score 1 · Answer 2 · answered Sep 26 '13 at 14:14

Use this, change the LookIn argument to xlValues instead of xlFormulas:

Selection.Find(What:="#REF!", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate

For a bit cleaner implementation:

Dim sht as Worksheet
Dim rngSrch as Range
Dim rngErr as Range

Set sht = Sheets("Location_Multiple")
Set rngSrch = sht.Range("A1:AL10000")

Set rngErr = rngSearch.Find(What:="#REF!", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not rngErr Is Nothing Then
    'Do something to the offending cell, like, highlight it:
    rngErr.Interior.ColorIndex = 39
End If

Anticipating there may be multiple cells with an error like this, you'll probably have to implement your .Find within a Do...While loop. There are several examples of that sort of problem on SO. If you have trouble implementing the loop, let me know.

score 0 · Answer 3 · answered Sep 26 '13 at 10:01

0

The problem you have is because you're searching for a string - whereas #REF is an error.

You could use the IsError function, to return true for a cell with an error. Combine this with a loop, and you could achieve what you require. I haven't tested this, but you get the jist:

Set rng = Sheets("Location_Multiple").Range("A1:AL10000")
For Each cell In rngError
    If IsError(cell) == true
    MsgBox "Please go back and check.... " & cell.Address
    Endif
Next

answered Sep 26 '13 at 10:01

Ste Pammenter

3,058
2
19
27

True. I didn't want to do all the homework. Exception handling would also be another feasible option. Depends how much work the OP wants to put in. – Ste Pammenter Sep 26 '13 at 10:09
2

only use one `=` in VBA :) – David Zemens Sep 26 '13 at 14:08

If #REF! is found then Error Box Macro

3 Answers3