Hi All,

I have written a code which enables me to lookup values from a huge csv file based on six different criteria. I have made the code so that it loops through the different rows that are filled. However at the moment, the code works quite slowly due to the looping. Is there a way to make it work faster?

Private Sub Get_Rate2()

Dim varCon As New ADODB.Connection
Dim rs As New ADODB.Recordset
Set varCon = New ADODB.Connection
Dim occ As String
Dim age As Integer
Dim tage As Integer
Dim *** As String
Dim smoker As String
Dim index As String
Dim dp As String
Dim i As Integer


varCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=source location;" & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
varCon.Open
rs.ActiveConnection = varCon

For i = 20 To 30

occ = Range("C" & i).Value
age = Range("J" & i).Value
tage = Range("I" & i).Value
*** = Range("D" & i).Value
smoker = Range("E" & i).Value
index = Range("F" & i).Value
dp = Range("L" & i).Value


rs.Source = "SELECT Rate FROM `sourcefile` where occupation = '" & occ & "' AND *** = '" & *** & "' AND smoker = '" & smoker & "' AND indexation = '" & index & "' AND defper = " & dp & " AND tage = " & tage & " AND age_nb = " & age & ""
rs.Open , varCon
Range("AK" & i).CopyFromRecordset rs
rs.Close

Next i

End Sub