Hi everyone,

I have an excel file that has data for different items throughout the year. What I'm trying to do is get averages per month per item and write it at the bottom of the file. I am doing this by connecting to a SQL Server and "plotting" the data in the excel sheet. This is what I have so far:
Sub EPData()

Dim InputYear As String
Dim StartDate As Date
Dim EndDate As Date
Dim DateLooper As Date
Dim conn As ADODB.Connection
Dim strSQL As String
Dim LastRow As Integer
Dim i As Integer
Dim InputDate As Date


InputYear = InputBox("Please enter the year of interest", "Select Year")
 
  
    
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  
  LastRow = LastRow - 5
  
 

Sheets(InputYear).Activate

InputDate = InputBox("Enter a startDate", "Date")
StartDate = InputDate
EndDate = Date


Range("A1") = "Sample Date"
Range("B1") = "1707"
Range("C1") = "1708"
Range("D1") = "1710"


Rows(1).Font.Bold = True
Columns("A:R").EntireColumn.AutoFit

Set conn = New ADODB.Connection

conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;DRIVER=SQL Server;SERVER=10.11.11.11;UID=UserID;PWD=Password"

conn.ConnectionTimeout = 0

conn.Open

strSQL = "select sam_res.sam_id, loc_id, colldate, long_des, res_numeric "
    strSQL = strSQL & "FROM sam_res, result_data, mnemonic WHERE sam_res.sam_id = result_data.sam_id AND "
    strSQL = strSQL & "mne_name = res_name AND long_des = 'Chlorine Residual Total' AND loc_id in ('1707','1708','1710','1712','1713','1715','1718','1719','2703','2704','2712','2713','2716','7200','7201','7207') AND "
    strSQL = strSQL & "colldate between '" & StartDate & "' AND '" & EndDate & "'  "
    strSQL = strSQL & "ORDER BY colldate asc"
    
    Set RS = conn.Execute(strSQL)
    
  
    Do While RS.EOF = False

    Select Case RS!loc_id
        
        Case "1707"
        
        For i = 2 To LastRow
            If RS!colldate = Range("A" & i) Then
            Range("B" & i).Value = RS!res_numeric
            End If
            
            Next i
            
               Case "1708"
        
        For i = 2 To LastRow
            If RS!colldate = Range("A" & i) Then
            Range("C" & i).Value = RS!res_numeric
            End If
            
            Next i
            
               Case "1710"
        
        For i = 2 To LastRow
            If RS!colldate = Range("A" & i) Then
            Range("D" & i).Value = RS!res_numeric
            End If
            
            Next i
            
            
            
    End Select
RS.MoveNext


Loop
 
RS.Close

conn.Close

Set conn = Nothing

For i = 2 To LastRow

If Range("B" & i).Value < 1 Then
    Range("B" & i).Font.Color = vbRed
    End If
    
    
    If Range("C" & i).Value < 1 Then
    Range("C" & i).Font.Color = vbRed
    End If
    If Range("D" & i).Value < 1 Then
    Range("D" & i).Font.Color = vbRed
    End If
    If Range("E" & i).Value < 1 Then
    Range("E" & i).Font.Color = vbRed
    End If
    If Range("F" & i).Value < 1 Then
    Range("F" & i).Font.Color = vbRed
    End If
    If Range("G" & i).Value < 1 Then
    Range("G" & i).Font.Color = vbRed
    End If
    If Range("H" & i).Value < 1 Then
    Range("H" & i).Font.Color = vbRed
    End If
    If Range("I" & i).Value < 1 Then
    Range("I" & i).Font.Color = vbRed
    End If
    If Range("J" & i).Value < 1 Then
    Range("J" & i).Font.Color = vbRed
    End If
    If Range("K" & i).Value < 1 Then
    Range("K" & i).Font.Color = vbRed
    End If
    If Range("L" & i).Value < 1 Then
    Range("L" & i).Font.Color = vbRed
    End If
    If Range("M" & i).Value < 1 Then
    Range("M" & i).Font.Color = vbRed
    End If
    If Range("O" & i).Value < 1 Then
    Range("O" & i).Font.Color = vbRed
    End If
    If Range("P" & i).Value < 1 Then
    Range("P" & i).Font.Color = vbRed
    End If
    If Range("Q" & i).Value < 1 Then
    Range("Q" & i).Font.Color = vbRed
   
    End If
    
    Next i


Range("A" & LastRow + 2) = "Minimum"
Range("A" & LastRow + 3) = "Maximum"
Range("A" & LastRow + 4) = "Average"
Range("A" & LastRow + 5) = "90th Percentile"

Range("B" & LastRow + 2) = WorksheetFunction.Min(Range("b2:b" & LastRow))
Range("B" & LastRow + 3) = WorksheetFunction.Max(Range("b2:b" & LastRow))
Range("B" & LastRow + 4) = WorksheetFunction.Average(Range("b2:b" & LastRow))
Range("B" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("b2:b" & LastRow), 0.9)

Range("C" & LastRow + 2) = WorksheetFunction.Min(Range("c2:c" & LastRow))
Range("c" & LastRow + 3) = WorksheetFunction.Max(Range("c2:c" & LastRow))
Range("c" & LastRow + 4) = WorksheetFunction.Average(Range("c2:c" & LastRow))
Range("c" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("c2:c" & LastRow), 0.9)

Range("d" & LastRow + 2) = WorksheetFunction.Min(Range("d2:d" & LastRow))
Range("d" & LastRow + 3) = WorksheetFunction.Max(Range("d2:d" & LastRow))
Range("d" & LastRow + 4) = WorksheetFunction.Average(Range("d2:d" & LastRow))
Range("d" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("d2:d" & LastRow), 0.9)

Range("e" & LastRow + 2) = WorksheetFunction.Min(Range("e2:e" & LastRow))
Range("e" & LastRow + 3) = WorksheetFunction.Max(Range("e2:e" & LastRow))
Range("e" & LastRow + 4) = WorksheetFunction.Average(Range("e2:e" & LastRow))
Range("e" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("e2:e" & LastRow), 0.9)

Range("f" & LastRow + 2) = WorksheetFunction.Min(Range("f2:f" & LastRow))
Range("f" & LastRow + 3) = WorksheetFunction.Max(Range("f2:f" & LastRow))
Range("f" & LastRow + 4) = WorksheetFunction.Average(Range("f2:f" & LastRow))
Range("f" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("f2:f" & LastRow), 0.9)

Range("g" & LastRow + 2) = WorksheetFunction.Min(Range("g2:g" & LastRow))
Range("g" & LastRow + 3) = WorksheetFunction.Max(Range("g2:g" & LastRow))
Range("g" & LastRow + 4) = WorksheetFunction.Average(Range("g2:g" & LastRow))
Range("g" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("g2:g" & LastRow), 0.9)

Range("h" & LastRow + 2) = WorksheetFunction.Min(Range("h2:h" & LastRow))
Range("h" & LastRow + 3) = WorksheetFunction.Max(Range("h2:h" & LastRow))
Range("h" & LastRow + 4) = WorksheetFunction.Average(Range("h2:h" & LastRow))
Range("h" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("h2:h" & LastRow), 0.9)

Range("i" & LastRow + 2) = WorksheetFunction.Min(Range("i2:i" & LastRow))
Range("i" & LastRow + 3) = WorksheetFunction.Max(Range("i2:i" & LastRow))
Range("i" & LastRow + 4) = WorksheetFunction.Average(Range("i2:i" & LastRow))
Range("i" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("i2:i" & LastRow), 0.9)

Range("j" & LastRow + 2) = WorksheetFunction.Min(Range("j2:j" & LastRow))
Range("j" & LastRow + 3) = WorksheetFunction.Max(Range("j2:j" & LastRow))
Range("j" & LastRow + 4) = WorksheetFunction.Average(Range("j2:j" & LastRow))
Range("j" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("j2:j" & LastRow), 0.9)

Range("k" & LastRow + 2) = WorksheetFunction.Min(Range("k2:k" & LastRow))
Range("k" & LastRow + 3) = WorksheetFunction.Max(Range("k2:k" & LastRow))
Range("k" & LastRow + 4) = WorksheetFunction.Average(Range("k2:k" & LastRow))
Range("k" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("k2:k" & LastRow), 0.9)

Range("l" & LastRow + 2) = WorksheetFunction.Min(Range("l2:l" & LastRow))
Range("l" & LastRow + 3) = WorksheetFunction.Max(Range("l2:l" & LastRow))
Range("l" & LastRow + 4) = WorksheetFunction.Average(Range("l2:l" & LastRow))
Range("l" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("l2:l" & LastRow), 0.9)

Range("m" & LastRow + 2) = WorksheetFunction.Min(Range("m2:m" & LastRow))
Range("m" & LastRow + 3) = WorksheetFunction.Max(Range("m2:m" & LastRow))
Range("m" & LastRow + 4) = WorksheetFunction.Average(Range("m2:m" & LastRow))
Range("m" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("m2:m" & LastRow), 0.9)

Range("o" & LastRow + 2) = WorksheetFunction.Min(Range("o2:o" & LastRow))
Range("o" & LastRow + 3) = WorksheetFunction.Max(Range("o2:o" & LastRow))
Range("o" & LastRow + 4) = WorksheetFunction.Average(Range("o2:o" & LastRow))
Range("o" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("o2:o" & LastRow), 0.9)

Range("p" & LastRow + 2) = WorksheetFunction.Min(Range("p2:p" & LastRow))
Range("p" & LastRow + 3) = WorksheetFunction.Max(Range("p2:p" & LastRow))
Range("p" & LastRow + 4) = WorksheetFunction.Average(Range("p2:p" & LastRow))
Range("p" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("p2:p" & LastRow), 0.9)

Range("q" & LastRow + 2) = WorksheetFunction.Min(Range("q2:q" & LastRow))
Range("q" & LastRow + 3) = WorksheetFunction.Max(Range("q2:q" & LastRow))
Range("q" & LastRow + 4) = WorksheetFunction.Average(Range("q2:q" & LastRow))
Range("q" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("q2:q" & LastRow), 0.9)

Range("n" & LastRow + 2) = WorksheetFunction.Min(Range("n2:n" & LastRow))
Range("n" & LastRow + 3) = WorksheetFunction.Max(Range("n2:n" & LastRow))
Range("n" & LastRow + 4) = WorksheetFunction.Average(Range("n2:n" & LastRow))
Range("n" & LastRow + 5) = WorksheetFunction.Percentile_Inc(Range("n2:n" & LastRow), 0.9)


If Range("r2").Value = "" Then
    For i = 2 To LastRow
    Range("r" & i) = month(Range("a" & i))
    Next i
    
    End If
End Sub
I've tried to do it using Select Case but I can't think of how to relate the individual months with the individual comments.

Any ideas are much appreciated.

Xhuljano