Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Data Last Row Loop through cells Excel 2000 & 2003 | Excel Programming | |||
How to loop thru cells in a row | Excel Programming | |||
Help - loop through cells in a range that are not together (several different cells as Target) | Excel Programming | |||
Loop cells - get all rows with matching data - paste into different wb | Excel Programming | |||
VBA loop cells | Excel Programming |