VBA Spread Sheet Seach w/Query

[playern07]

Hello, I have a VBA code I have been working on and I would like it to look at a row of information in sheet1 and plug in the identified fields into a query. I would like it to continue to loop through each row until there is no more criteria to be run. I also would like it to paste the output on sheet2 sheet. To my understanding, every time the code runs the query a row of data will be returned and it deletes the previous query results instead of adding the next query's data directly under the previous results. I am not sure how to make the code copy the previous data and paste the values to sheet2 while adding the next query's information to the next available row of the sheet. Any help is greatly appreciated.

Sub DairyQRY()
Dim Catergory As String
Worksheets("Sheet1").Activate
Category = Cells(2, "D").Value

If Category = "Bread" Then
Category = 1402
If Category = "Butter" Then
Category = 1403
If Category = "Creamers" Then
Category = 1405
If Category = "Yogurt" Then
Category = 1406
If Category = "Desserts" Then
Category = 1407
If Category = "Eggs/Pickles" Then
Category = "1408,1412,1413"
If Category = "Milk" Then
Category = "1410,1411"
If Category = CreamCheese Then
Category = 1415
If Category = Other Then
Category = "1409,1416,1495"
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Query Assignment Strings
Dim DairyQRY As String

Sheet2.Select
Columns("A:F").Select
Selection.ClearContents
Range("A1").Select

'assign all items to IN clause in query

Sheet1.Select
Dim FacilityIDQRY As String
Dim cell1 As Range
FacilityIDQRY = Sheet1.Range("$A$2")
For Each cell1 In Range("OFFSET($A$3,0,0,COUNT($A:$A)-1,1)").Cells
FacilityIDQRY = FacilityIDQRY & "," & cell1.Value
Next

Sheet1.Select
Dim DateQRY As Long, i As Long, s As String
DateQRY = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To DateQRY
s = s & Format(Range("B" & i).Value, "'yyyy-mm-dd'") & ", "
Next i
Range("C1").Value = Left(s, Len(s) - 2)
Sheet1.Select
Dim StartTimeQRY As String
Dim cell3 As Range
StartTimeQRY = Sheet1.Range("$E$2")
For Each cell1 In Range("OFFSET($E$3,0,0,COUNT($E:$E)-1,1)").Cells
StartTimeQRY = StartTimeQRY & "," & cell1.Value
Next

Sheet1.Select
Dim EndTimeQRY As String
Dim cell4 As Range
EndTimeQRY = Sheet1.Range("$F$2")
For Each cell1 In Range("OFFSET($F$3,0,0,COUNT($F:$F)-1,1)").Cells
EndTimeQRY = EndTimeQRY & "," & cell1.Value
Next

'build query in text format

DairyQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'02-Bread ' as Category, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"ProdDimV02C.SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = " & FacilityIDQRY & " " & _
"AND STIL.SALES_TRANS_DT = " & Range("C1").Value & " " & _
"AND STIL.SALES_TRANS_TM Between " & StartTimeQRY & " and " & EndTimeQRY & " " & _
"AND ID.CATEGORY_ID = 1402 " & _
"GROUP BY " & _
"1,2,3,4) "
Sheet2.Select
'connect to ODBC and run query

 

[CharlesChuckieCharles]

I think there is too much info not in your example

But as a couple of pointers

instead of

If Category = "Bread" Then
Category = 1402
If Category = "Butter" Then
Category

try Select Case

Select Case Category
Case "Bread"
Category = "1402"

Case "Butter"
Category = "1403"
....
Case Else
Category = "your others"
End Select


Also in the SQL

Look up the IN(.... clause

 

[playern07]

Thank you for the tips. Below you will find the connection portion of my code which is the only thing I left out. In the mean time I wll be looking over the "IN" function.

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"ODBC;DSN=Teradata Production;;; AUTHENTICATION=;AUTHENTICATONPARAMETER=;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = DairyQRY
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "ItemQuery"
End With

Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

 

[playern07]

Here is some more clarification...

Sample Sheet1 info:

<TABLE style="WIDTH: 380pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=506 border=0><COLGROUP><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=86><COL style="WIDTH: 105pt; mso-width-source: userset; mso-width-alt: 5120" width=140><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 39pt; COLOR: white; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=52 height=17>Store</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 53pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=71>Date</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; COLOR: white; BORDER-BOTTOM: white 1.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=86>Time Period</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 105pt; COLOR: white; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=140>Category</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; COLOR: white; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=81>Start Time</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #4f81bd; BORDER-LEFT: #d4d0c8; WIDTH: 57pt; COLOR: white; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=76>End Time</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: white 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: #b8cce4" align=right height=17>356</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>2011-07-20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Afternoon</TD><TD class=xl67 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: white 0.5pt solid; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Bread </TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>708</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>1524</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=17>356</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>2011-07-20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Afternoon</TD><TD class=xl66 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: white 0.5pt solid; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Butter</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>709</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1526</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right height=17>356</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>2011-07-20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Afternoon</TD><TD class=xl68 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: white 0.5pt solid; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">Creamers</TD><TD class=xl63 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: white 0.5pt solid; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>710</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: white; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: white 0.5pt solid; BACKGROUND-COLOR: #b8cce4" align=right>1528</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; HEIGHT: 12.75pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right height=17>356</TD><TD class=xl65 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>2011-07-20</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Afternoon</TD><TD class=xl66 style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: white 0.5pt solid; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none">Yogurt</TD><TD style="BORDER-RIGHT: white 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #dbe5f1; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>711</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: white 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #b8cce4; BORDER-LEFT: #d4d0c8; COLOR: black; BORDER-BOTTOM: white 0.5pt solid; FONT-FAMILY: Arial; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>1530</TD></TR></TBODY></TABLE>

I want the code to go through each row and fill in the store, date, start time, end time, and category as criteria. My trouble comes from the way I assigned the variable. I cannot figure out how to tell it to keep running the query corresponding to the number of populated rows present.

Also, I edited my query code to reflect the variable names.

DairyQRY = "(SELECT " & _
"STIL.FACILITY_ID, " & _
"STIL.SALES_TRANS_DT, " & _
"'Afternoon' as TimePeriod, " & _
"( 'Between_' || Trim(Min(STIL.SALES_TRANS_TM)) || '_' || Trim(Max(STIL.SALES_TRANS_TM)) ) as TimeCriteria, " & _
"Sum(STIL.ITEM_QTY) as TotalItemQty " & _
"FROM " & _
"ProdDimV02C.SALES_TRANS_ITEM_LINE as STIL, " & _
"ITEM_DIM as ID " & _
"WHERE " & _
"STIL.ITEM_ID = ID.ITEM_ID " & _
"AND STIL.UPC_SALES_TRANS_TYPE_CD In ('0','2','8') " & _
"AND STIL.FACILITY_ID = " & StoreVariable & " " & _
"AND STIL.SALES_TRANS_DT = " & DateVariable & " " & _
"AND STIL.SALES_TRANS_TM Between " & StartTimeVariable & " and " & EndTimeVariable & " " & _
"AND ID.CATEGORY_ID = " & Catergory & " " & _
"GROUP BY " & _
"1,2,3,4) "