Geek Answers Handbook

Error Handling with Range.Find () VBA

I answered question with this WORK code:

This vba code will go through all identifiers from the bottom up, if the fax number already exists, it will add the identifier to this row in columns C, D, E, etc. (always choosing the next empty one) and then delete the line. In the end, it replaces columns A and B, so you are left with the fax number in Col A and Cols B, C, D, E, etc. ALL IDS associated with this number.

Tested work:

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

: DestRng = DestRng? , , , , DestRng Cells.Value. , , DestRng = DestRng, , , . , . , Range.Find() , DestRng = DestRng .

EDIT: :

  • : DataBefore
  • DestRng = DestRng EntireRow.Delete : CommentOut
  • : Fullcode
+1
1

:

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


r=r

. - , r Nothing

r=r

:

r.Value=r.Value
+2

More articles:


All Articles