Macro for running the SQL multiple times

Currently the SQL will pick the value which is present in "Sheet2.Range("C2")" and execute the SQL and store the output into Selection.Offset(1, 1) = sourceRst.Fields("DT_KEY") .
My requirement is i will be having values in Sheet2.C3, C4, C5,and goes on. So, the SQL should pick the value 1 by 1 and execute it and populate the data into the corresponding DB Output field.

Kindly provide me the code which will make the SQL run 1 by 1 till it finds a NULL value in the Sheet2.C field.

I am attaching the Sheet below, kindly look into Sheet2.B column and Sheet2.C column.
Sheet2.C is the date which is passed into the SQL and Sheet2.B is the output from Database.

Sub Ora_Connection()

Dim sourceCnn As ADODB.Connection
Dim sourceRst As ADODB.Recordset

Set sourceCnn = New ADODB.Connection
Set sourceRst = New ADODB.Recordset

strCon = "Provider=OraOLEDB.Oracle;User ID=;Password=;Data Source="
  
sourceCnn.Open (strCon)
sourceRst.ActiveConnection = sourceCnn


sourceRst.Source = "select DT_KEY from dt_dim where CAL_DT = TO_DATE('" & Sheet2.Range("C2").Value & "', 'MM/DD/YYYY')"
sourceRst.Open

Sheets("Sheet2").Select

   Cells(lastrow, 1).Select
   Do While Not sourceRst.EOF
    Selection.Offset(1, 1) = sourceRst.Fields("DT_KEY")
  Selection.Offset(1, 0).Select
                   sourceRst.MoveNext
          Loop
  sourceRst.Close


End Sub

I would change your Ora_Connection to have an argument for dtDate, and then use this argument in the SQL query.

I would then loop through a range,

so

Set rngInspect = sheets("Sheet").range("1:1")
do until rngInspect.Cells(1,intOffset)=""
        ora_connection(rngInspect.Cells(1,intOffset).value)
        intOffset=intOffset+1
loop

set rngInspect=Nothing

Something like that. I would also look at keeping the connection open, so have another sub say, Open_DB and Close_DB, so before this Open_DB and at the end Close_DB, saves the opening each time.

Hope this helps

Nathan.