Difficulty With ListBoxes

[Cly]

I'm having some trouble with list boxes. What I am trying to do is run a series of queries against each item in a list box. The problem being, after the queries are ran, I want to export the specific results to an excel sheet in different rows all linear . The problem I am having is that I can run the queries in a loop from the list box, But I don't know how to post the results in different rows. Here is what I have come up with so far.....


It works, but it doesn't. I have tried incorporating the 'Offset' property in the loop structure, but that threw everything out of whack. Heh. ....ANY help would be greatly appreciated. Thanks!

Private Sub cmdExcel_Click()

Dim xlapp As Excel.Application
Dim xlbook As Excel.Workbook
Dim xlsheet As Excel.Worksheet
Dim xlrange As Excel.Range
Dim date1 As Date
Dim date2 As Date

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Set xlapp = CreateObject("Excel.Application")
date1 = InputBox$("Enter Start Date")
date2 = InputBox$("Enter End Date")
With xlapp
.Workbooks.Add
.Worksheets("Sheet1").Activate
For i = 0 To Me.lstReps.ListCount - 1

Select Case i

Case Is = 0
.Range("A1") = "Place For Persons's Row"
Dim rs As New ADODB.Recordset
Dim varrs As Variant
rs.Open "SELECT Count(tblSYSResults.ID) AS CountOfID FROM tblSYSResults HAVING (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep1)='" & Me.lstReps.ItemData(i) & "')) OR (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep2)='" & Me.lstReps.ItemData(i) & "'));", cnn, adOpenDynamic, adLockReadOnly
varrs = rs.GetRows
rs.Close
.Range("B1") = varrs(0, 0)

Case Is = 1
Dim rst As New ADODB.Recordset
Dim varrst As Variant
.Range("A2") = "Place for Second Persons Row"
rst.Open "SELECT Count(tblSYSResults.ID) AS CountOfID FROM tblSYSResults HAVING (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep1)='" & Me.lstReps.ItemData(i) & "')) OR (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep2)='" & Me.lstReps.ItemData(i) & "'));", cnn, adOpenDynamic, adLockReadOnly
varrst = rst.GetRows
rst.Close
.Range("B2") = varrst(0, 0)

Case Is = 2
.Range("A3") = "Place for Third Person's Row"
Dim rstJ As New ADODB.Recordset
Dim varrstJ As Variant
.Range("A2") = "Place for Gary's Row"
rstJ.Open "SELECT Count(tblSYSResults.ID) AS CountOfID FROM tblSYSResults HAVING (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep1)='" & Me.lstReps.ItemData(i) & "')) OR (((tblSYSResults.SYSResult)='Sale') AND ((tblSYSResults.SYSDate) Between #" & date1 & "# And #" & date2 & "#) AND ((tblSYSResults.SYSRep2)='" & Me.lstReps.ItemData(i) & "'));", cnn, adOpenDynamic, adLockReadOnly
varrstJ = rstJ.GetRows
rstJ.Close
.Range("B3") = varrstJ(0, 0)

End Select
Next

.Visible = True
End With

Set xlapp = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing
Set xlrange = Nothing
Set cnn = Nothing

End Sub