Do loop or For Next with data from cells
 

I have seen many examples on how to give cells values but I want to use the
value that is in a certain cell. I have a list of submission numbers that
are contained in two columns of data in Sheet 1 Cells A4:A40 and F4:F40. I
have a currently working macro that has an Application.InputBox where the
user types a submission number and a sql query is excuted for that submission
number. The information from the sql query gets placed in Sheet 3. I want to
excute this sql query for each of the submission numbers. So I need to
change the code that reads

mynum = Application.InputBox("Select Submission_ID")

to use each of the submissions contained in Cells A4:A40 and F4:F40.

Each time this sql query runs I want to use the countif fuction on some of
the data that returns from the query. Here the countif fuctions I am using.

=SUMPRODUCT((Sheet3!$F$2:$F$401<"")/COUNTIF(Sheet3!$F$2:$F$401,Sheet3!$F$2:$F$401&"")

=COUNTIF(Sheet3!$G$2:$G$401,"INCOMPLETE")

These fuctions work...I need to Copy and paste the values only uptained from
these fuctions into certain cells in Sheet 2. I know I will need to use a
Paste Special (values only) because if I leave the formula in the sheet and
use the regular copy paste then the formula gets copied too. Then each time
the sql query runs the information I looked up about previous submission gets
changed to the information about the current submission.
Then I need the macro to loop and query the next submission.

In Summary:

Do Sql Query for mySubmission
Use count fuction on data from query
Paste (values only) from count fuction
Loop 'needs to loop for each submission in A4:A40 then F4:F40

Thanks in advance for any guidance you can give.
Note: Submission numbers are too large to be called an interger.

Do loop or For Next with data from cells
 

Here is the Code for the Sql Query Macro:

Sub VIEW_TASK_DETAIL()
Dim i As Integer

Sheets("Sheet1").Select
mynum = Application.InputBox("Select Submission_ID")
Num = 1000 & mynum

Sheets("Sheet3").Select
Columns("A:O").Select
Selection.ClearContents
Range("A1").Select


With ActiveSheet.QueryTables.Add(Connection:= _

"ODBC;DSN=OurDNS;UID=TheUsersName;PWD=TheUsersPass word;SERVER=NameofServer;",
Destination:=Sheets _
("Sheet3").Range("A1"))
.CommandText = Array( Array contents)

.Name = "Query from OurDNS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Range("A1").Select
End Sub


"MovingBeyondtheRecordButton" wrote:

I have seen many examples on how to give cells values but I want to use the
value that is in a certain cell. I have a list of submission numbers that
are contained in two columns of data in Sheet 1 Cells A4:A40 and F4:F40. I
have a currently working macro that has an Application.InputBox where the
user types a submission number and a sql query is excuted for that submission
number. The information from the sql query gets placed in Sheet 3. I want to
excute this sql query for each of the submission numbers. So I need to
change the code that reads

mynum = Application.InputBox("Select Submission_ID")

to use each of the submissions contained in Cells A4:A40 and F4:F40.

Each time this sql query runs I want to use the countif fuction on some of
the data that returns from the query. Here the countif fuctions I am using.

=SUMPRODUCT((Sheet3!$F$2:$F$401<"")/COUNTIF(Sheet3!$F$2:$F$401,Sheet3!$F$2:$F$401&"")

=COUNTIF(Sheet3!$G$2:$G$401,"INCOMPLETE")

These fuctions work...I need to Copy and paste the values only uptained from
these fuctions into certain cells in Sheet 2. I know I will need to use a
Paste Special (values only) because if I leave the formula in the sheet and
use the regular copy paste then the formula gets copied too. Then each time
the sql query runs the information I looked up about previous submission gets
changed to the information about the current submission.
Then I need the macro to loop and query the next submission.

In Summary:

Do Sql Query for mySubmission
Use count fuction on data from query
Paste (values only) from count fuction
Loop 'needs to loop for each submission in A4:A40 then F4:F40

Thanks in advance for any guidance you can give.
Note: Submission numbers are too large to be called an interger.