Error Handling with Range.Find() VBA

Question

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:

Data Before:
Data with DestRng = DestRng AND EntireRow.Delete commented out:
Data with my full code:

I can't reproduce the issue. Removing the DestRng = DestRng doesn't change the behavior as far as I can tell. It is functional either way. What is the simplest set of data in the spreadsheet with which you can reproduce the issue? — Drew Gaynor
– Drew Gaynor, Commented Dec 8, 2014 at 15:03
Find doesn't error if the search value isn't found, it returns Nothing which is what you should test: If Not DestRng Is Nothing Then — Rory
– Rory, Commented Dec 8, 2014 at 15:17
@Rory My problem was more using some old code with a variable type that couldn't use Nothing so it triggered the error on the definition line. — Chrismas007
– Chrismas007, Commented Dec 8, 2014 at 15:25
@Chrismas007 I'm confused. You can't assign the result of a Find to a variable type that can't be set to Nothing. — Rory
– Rory, Commented Dec 8, 2014 at 15:28

Gary's Student · Accepted Answer · 2014-12-08 15:16:25Z

3

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

answered Dec 8, 2014 at 15:16

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Chrismas007 Over a year ago

Thanks, I was using code from creating a new sheet if one doesn't exist and in that case you can't set createNewSheet = Nothing as it Errors. Glad to know why that works then.

cheezsteak Over a year ago

Please mention that Range defaults to .value because it is the default member property of Range. This behavior occurs with all objects that have a default member property.

Chrismas007 Over a year ago

Would it be more appropriate for me to use DestRng.Value = DestRng.Value to trigger the error in case the default doesn't trigger? Otherwise I could just rewrite it as If Not Nothing Then do stuff Else Next

Collectives™ on Stack Overflow

Error Handling with Range.Find() VBA

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related