Reputation: 133
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
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
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