macro correction

[Tk1051]

here is the rule its based on
in tab 1 named (Not on a Category) column D (Vendors) is a list of random vendors. In tab 3 named (Vendor Policies) column A (Vendor Desc) is a list of every vendor Any vendors in the Not on a Category tab that matches the vendors in the Vendor Policy (column A) and has a NO in column M. Write TT in column H of the Not on a Category Tab ONLY if column W in tab 1 says the word Damaged.. macro times out...can someone help me fix it? thanks

>>> use code - tags <<<

Sub DMG()
    Dim ws1 As Worksheet, ws3 As Worksheet
    Dim lastRow1 As Long, lastRow3 As Long
    Dim i As Long, j As Long

    Set ws1 = ThisWorkbook.Sheets("Not on a Category")
    Set ws3 = ThisWorkbook.Sheets("Vendor Policies")

    lastRow1 = ws1.Cells(ws1.Rows.Count, "D").End(xlUp).Row
    lastRow3 = ws3.Cells(ws3.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow1
        For j = 2 To lastRow3
            If ws1.Cells(i, "D").Value = ws3.Cells(j, "A").Value And ws3.Cells(j, "M").Value = "NO" And ws1.Cells(i, "W").Value = "Damaged" Then
                ws1.Cells(i, "H").Value = "TT"
                Exit For
            End If
        Next j
    Next i
End Sub

 

[Marc L]

What's the issue with your working 'so so' logic VBA procedure ?​

​

IMO it must be trashed rather than to be corrected ! (common case of what never do under Excel VBA …)​

Better restart from blank for an instant result with an appropriate Excel basics logic rather than such slowest looping code …​

​

 

[Tk1051]

Thanks for input Marc L

 

[Marc L]

You did not answer to my question …​

​