Consulting

Results 1 to 2 of 2

Thread: MATCHING CODE

  1. #1
    VBAX Newbie
    Joined
    Jul 2018
    Posts
    4
    Location

    MATCHING CODE

    Hi Pro,

    I have this code from this forum.
    Now i would like to modify this code as per my current requirement.
    Can someone explain to me this code?
    Beside delete of column M which will only left the no with "3000". What else they will delete?

    Sub MATCHING()    Dim a, i As Long, x As Range, dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        With Sheets("AC")
            .AutoFilterMode = False
            a = .Range("m3", .Range("m" & Rows.Count).End(xlUp)).Value
            For i = 1 To UBound(a, 1)
                If Not dic.exists(a(i, 1)) Then
                    dic(a(i, 1)) = Empty
                Else
                    If x Is Nothing Then
                        Set x = .Rows(i + 2)
                    Else
                        Set x = Union(x, .Rows(i + 2))
                    End If
                End If
            Next
            If Not x Is Nothing Then x.EntireRow.Delete
            With .Range("m2", .Range("m" & Rows.Count).End(xlUp))
                .AutoFilter 1, "<>3000*"
                .Offset(1).EntireRow.Delete
                .AutoFilter
            End With
            With .Range("m2", .Range("m" & Rows.Count).End(xlUp))
                If .Rows.Count > 1 Then
                    .Offset(1, 2).Resize(.Rows.Count - 1).Formula = _
                    "=if(trim(n3)="""",""PX"",if(iserror(match(n3,'TC-CATS'!s:s,0)),""X"",""""))"
                End If
            End With
        End With
        MsgBox "Match Completed! " & vbCrLf & ""
    End Sub
    TIA

  2. #2
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,849
    I note you didn't acknowledge help you received here previously.
    How can you expect more help?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •