Speed issue

[mrosendal]

Hi all

I have written some code that works but its kind of slow-ish. My teacher solved the same problem and his works much faster. I am however not able to view his code.

The task is to find flight routes from a list of 10000 flight numbers. Routes have to have origin in the cities typed into column E and destination at cities listed in Column F.

I am using arrays To contain dest. origin and flight numbers.My Code takes about 10 seconds to run my teachers only a second or so....

Sub FindFlights()

Dim Origin() As String, Destination() As String, FligtNumber() As String
Dim Arraylenght As Integer, NumRows As Integer, i As Integer, j As Integer, t As Integer, p As Integer

With Range("A1")

Arraylenght = Range(.Offset(1, 0), .End(xlDown)).Rows.Count

End With

ReDim Origin(Arraylenght)
ReDim Destination(Arraylenght)
ReDim FlightNumber(Arraylenght)

For i = 1 To Arraylenght

Origin(i) = Range("A" & i + 1).Value
Destination(i) = Range("B" & i + 1).Value
FlightNumber(i) = Range("C" & i + 1).Value

Next

With Range("E1")

NumRows = Range(.Offset(0, 0), .End(xlDown)).Rows.Count

End With

t = 1

For j = 2 To NumRows

For i = 1 To Arraylenght

For p = 2 To NumRows

If Origin(i) = Range("E" & j).Value And Destination(i) = Range("F" & p).Value Then
t = t + 1
Range("H" & t).Value = Origin(i)
Range("I" & t).Value = Destination(i)
Range("J" & t).Value = FlightNumber(i)
End If

Next
Next

Next

[tigeravatar]

mrosendal,

For something like this, I highly recommend using the AutoFilter method:

Please Login or Register  to view this content.

[snb]

or

Please Login or Register  to view this content.

[mrosendal]

Thank you Tiger

Your code is really fast. Even adding more cities does not reduce the speed drastically. Still slightly new at this so its nice to get some tips from the pros... How long have you been programming for ?