vendredi 27 juillet 2018

For 70,000 rows, my macro is taking a lot of time to give output comment as per various conditions(if statement). Please help me improve my approach

For traversing 70,000 rows(using for loop), my macro is taking a lot of time to give output comment as per various conditions(if statement). Please help me improve my approach.I am working on around 70,000 rows and my code is taking a lot of time to run. Please see the attachment image that depicts how data looks. Can someone please guide me with a better approach here?enter image description here This code compares various columns and gives output on the basis of certain conditions.

Set wsa = ThisWorkbook.Sheets("Audit-NIMS vs Site Topology")

With wsa
AudLastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
.Cells(1, AudLastCol + 1).Value = "Match;Issue Type;Actions"

For l = 2 To AudLastRow
    aPRTS = .Cells(l, AudLastCol).Value
    bNIMS = .Cells(l, NIMsLastCol).Value
    tempin = .Cells(l, 2).Value

    If aPRTS = bNIMS Then
        Deployed19 = Application.Match("Deployed(1.9)", .Rows(1), 0)
        If IsNumeric(Deployed19) Then
            d19 = .Cells(l, Deployed19).Value
        Else
            d19 = 0
        End If
        Deployed800 = Application.Match("Deployed (800)", .Rows(1), 0)
        If IsNumeric(Deployed800) Then
            d8 = .Cells(l, Deployed800).Value
        Else
            d8 = 0
        End If
        Deployed2500 = Application.Match("Deployed (2.5)", .Rows(1), 0)
        If IsNumeric(Deployed2500) Then
            d25 = .Cells(l, Deployed2500).Value
        Else
            d25 = 0
        End If

        PRTS800 = Application.Match("Total-800-PRTS", .Rows(1), 0)
        If IsNumeric(PRTS800) Then
            p8 = .Cells(l, PRTS800).Value
        Else
            p8 = 0
        End If

        PRTS1900 = Application.Match("Total-1900-PRTS", .Rows(1), 0)
        If IsNumeric(PRTS1900) Then
            p19 = .Cells(l, PRTS1900).Value
        Else
            p19 = 0
        End If

        PRTS2500 = Application.Match("Total-2500-PRTS", .Rows(1), 0)
        If IsNumeric(PRTS2500) Then
            p25 = .Cells(l, PRTS2500).Value
        Else
            p25 = 0
        End If
        If (p19 = d19) And (p8 = d8) And (p25 = d25) Then
            .Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
        Else
            .Cells(l, AudLastCol + 1).Value = "FALSE;Both;Update NIMS and PRTS."
        End If
    ElseIf aPRTS = "NA" And bNIMS = "0" Then
        .Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
    ElseIf aPRTS = "0" And bNIMS = "NA" Then
        .Cells(l, AudLastCol + 1).Value = "TRUE;None;No Action Required."
    ElseIf aPRTS > 0 And bNIMS = "NA" Then
        .Cells(l, AudLastCol + 1).Value = "N/A;NIMS;Update NIMS."
    ElseIf bNIMS > 0 And aPRTS = "NA" Then
        .Cells(l, AudLastCol + 1).Value = "N/A;PRTS;Check traffic from PRTS & Report to PRTS Team."
    ElseIf bNIMS > aPRTS Then
        .Cells(l, AudLastCol + 1).Value = "FALSE;PRTS;Check traffic from PRTS & Report to PRTS Team."
    ElseIf bNIMS < aPRTS Then
        .Cells(l, AudLastCol + 1).Value = "FALSE;NIMS;Update NIMS."
    End If
    'To compare certain category of Name and concatenate output accordingly.
    If InStr(1, .Cells(l, 1).Value, "52XC") > 0 Then
        .Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "Clearwire Site."
    ElseIf InStr(1, .Cells(l, 1).Value, "82XC") > 0 Then
        .Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "Clearwire Site."
    ElseIf InStr(1, .Cells(l, 1).Value, "XT") > 0 Then
        .Cells(l, AudLastCol + 1).Value = .Cells(l, AudLastCol + 1).Value & "COW Site."
    End If

    If bNIMS = "NA" And Application.CountIf(ThisWorkbook.Sheets("NIMS dump-SC level").Range("B1:B" & temprow), .Cells(l, 2).Value) Then
        .Cells(l, AudLastCol + 1).Value = Cells(l, AudLastCol + 1).Value & "Present in NIMS Dump."
    End If
Next l
End With

Aucun commentaire:

Enregistrer un commentaire