cyee

09-28-2018, 12:32 AM

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

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