jeudi 24 septembre 2020

The way to speed up multiple ElseIf queries in VBA

I've been working on this code all day and have finally gotten everything to work perfectly. The only problem, is that the code does run pretty slow. Considering that it will be used on a workbook with thousands of rows I would like to change that. I am extremely new to vba so there is probably stuff in here that is wrong or seems like a bad shortcut. I think I added a couple of ways that could speed it up but i didnt know if anything else could be done.

Option Explicit

Public Sub CL_NC()

Dim lZeile  As Long

With ThisWorkbook.Worksheets("Gesamt")

For lZeile = 2 To .Cells(.Rows.Count, 1).End(xlUp).Row
  
If InStr(.Range("D" & lZeile).Value, "Malaysia") > 0 And (InStr(.Range("B" & lZeile).Value, "ISO 9001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 14001") > 0 Or InStr(.Range("B" & lZeile).Value, "BS OHSAS 18001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 45001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 50001") > 0) Then
        .Range("G" & lZeile).Value = "Malaysia IMS CL"
ElseIf InStr(.Range("D" & lZeile).Value, "Malaysia") > 0 And InStr(.Range("C" & lZeile).Value, "IMS") > 0 Then
        .Range("G" & lZeile).Value = "Malaysia IMS NC"
ElseIf InStr(.Range("D" & lZeile).Value, "Malaysia") > 0 Then
        .Range("G" & lZeile).Value = "Malaysia " & .Range("C" & lZeile).Value & " NC"

ElseIf InStr(.Range("D" & lZeile).Value, "Indonesien") > 0 And (InStr(.Range("B" & lZeile).Value, "ISO 9001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 14001") > 0 Or InStr(.Range("B" & lZeile).Value, "BS OHSAS 18001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 45001") > 0 Or InStr(.Range("B" & lZeile).Value, "ISO 50001") > 0) Then
        .Range("G" & lZeile).Value = "Indonesien IMS CL"
ElseIf InStr(.Range("D" & lZeile).Value, "Indonesien") > 0 And InStr(.Range("C" & lZeile).Value, "IMS") > 0 Then
        .Range("G" & lZeile).Value = "Indonesien IMS NC"
ElseIf InStr(.Range("D" & lZeile).Value, "Indonesien") > 0 Then
        .Range("G" & lZeile).Value = "Indonesien " & .Range("C" & lZeile).Value & " NC"


        
     Else
        .Range("G" & lZeile).Value = ""
     End If
  Next lZeile
End With

End Sub

Aucun commentaire:

Enregistrer un commentaire