View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
MovingBeyondtheRecordButton MovingBeyondtheRecordButton is offline
external usenet poster
 
Posts: 20
Default 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.