CODEWITHSUNDEEP
performancevbaif-statement
Zapata
Zapata

Reputation: 133

iF Then Else code - how to make this run faster? VBA

I have a simple code which takes a long time to run. I was wondering if there is any way to make this run faster? Maybe this part (Cells(i, "U").Value = Cells(n, "X").Value) should not be used 2 times! Thanks!

For n = 3 To time_frame + 3
For i = 3 To 1002

If (Cells(i, "U").Value = Cells(n, "X").Value) And (Bed_in_use < 24) And Wait_L > 0 Then
Wait_L = Wait_L - (24 - Bed_in_use)
ElseIf (Cells(i, "U").Value = Cells(n, "X").Value) And (Bed_in_use < 24) And Wait_L <= 0 Then
Bed_in_use = Bed_in_use + 1
End If
Next i
Next n

MsgBox "The number of bed in use is " & Bed_in_use & ". There are " & Wait_L & " patients in the waiting list."

 End Sub

Upvotes: 0

Views: 125

Answers (2)

Comintern
Comintern

Reputation: 22205

Couple things will speed this up - The first was mentioned in the comments by @jcarroll, pulling the cells you need into an array and using that instead of making repeated calls to Cells.

The second is what you mentioned, structuring your If statements in a way that you aren't making the same comparisons twice. For example, this has to be true for either condition...

Cells(i, "U").Value = Cells(n, "X").Value

...and this always has to be true:

Bed_in_use < 24

After Bed_in_use is 24 (or higher), you can exit out of the loop because you'll never satisfy either the If or the ElseIf statement. I'd re-roll it into something like this:

Dim values() As Variant
values = ActiveSheet.UsedRange  '...or whatever Range you need.

For n = 3 To time_frame + 3
    If Bed_in_use >= 24 Then Exit For
    For i = 3 To 1002
        If Bed_in_use >= 24 Then Exit For
        If values(i, 21).Value = values(n, 24).Value Then
            If Wait_L > 0 Then
                Wait_L = Wait_L - (24 - Bed_in_use)
            Else
                Bed_in_use = Bed_in_use + 1
            End If
        End If
    Next i
Next n

Upvotes: 2

jcarroll
jcarroll

Reputation: 577

I'm not totally sure what your code is trying to do. But here is a sample of how you would compare two lists, and keep track of the total matches.

Sub test()

    Dim arrayU() As Variant
    Dim arrayX() As Variant

    Dim LrowU As Integer
    Dim LrowX As Integer

    Dim i As Integer
    Dim j As Integer

    Dim bed_in_use As Integer

    LrowU = Columns(21).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LrowX = Columns(24).Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    ReDim arrayU(1 To LrowU)
    ReDim arrayX(1 To LrowX)

    For i = 1 To LrowU
        arrayU(i) = Cells(i, 21)
    Next i

    i = 1

    For i = 1 To LrowX
        arrayX(i) = Cells(i, 24)
    Next i

    i = 1
    j = 1

    For i = 1 To LrowX
        For j = 1 To LrowU
            If arrayX(i) = arrayU(j) Then bed_in_use = bed_in_use + 1
        Next j
    Next i

    MsgBox (bed_in_use)

End Sub

Upvotes: 0

Related Questions