CODEWITHSUNDEEP
vbaexcel
Chrismas007
Chrismas007

Reputation: 6105

Error Handling with Range.Find() VBA

I answered a question with this WORKING code:

This vba code will loop through all IDs from the bottom up, if the fax number already exists, it will ADD the ID to that line in columns C, D, E, etc (always choosing the next blank) and then delete the line. At the end it swaps columns A and B so you are left with a fax number in Col A and Cols B, C, D, E, etc are ALL IDs associated with that number.

Tested Working:

Sub RemoveDups()

Dim CurRow As Long, LastRow As Long, DestLast As Long, DestRng As Range

LastRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
 For CurRow = LastRow To 3 Step -1
     Set DestRng = Range("B2:B" & CurRow - 1).Find(Range("B" & CurRow).Value, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlNext)
        DestRng = DestRng
     If Err > 0 Then
        Err.Clear
     Else
        DestLast = Cells(DestRng.Row, Columns.Count).End(xlToLeft).Column + 1
        Cells(DestRng.Row, DestLast).Value = Cells(CurRow, 1).Value
        Cells(CurRow, 1).EntireRow.Delete xlShiftUp
     End If
 Next CurRow
Columns("B:B").Cut
Columns("A:A").Insert Shift:=xlToRight
Application.CutCopyMode = False

MsgBox "Done"

End Sub

My question is this: Why do I need the extra line DestRng = DestRng? The reason I added it is because the code was non-functioning, so I added a check to dump DestRng into a Cells.Value. All the sudden, the code worked, so I made a simple DestRng = DestRng line to ensure there was still code that could error. If I comment out that line, it goes back to non-functioning code. Trying to figure out the reason why the Range.Find() will not activate the Error handler, but the DestRng = DestRng will activate the Error handler.

EDIT: Screenshots:

Upvotes: 1

Views: 8458

Answers (1)

Gary's Student
Gary's Student

Reputation: 96791

If you start with a completely empty worksheet and run:

Sub errorTest()
    Dim r As Range
    Set r = Cells.Find(what:="something", after:=Range("A1"))
    r = r
End Sub

The code will fail on the 

r=r

line. That is because the Set command sets r to Nothing and 

r=r

is equivalent to:

r.Value=r.Value

Upvotes: 3

Related Questions