Solved

create a summary excel tab

Posted on 2014-12-16
5
98 Views
Last Modified: 2015-01-26
i want to create a summary tab after the user makes their selection in the rich text box.  How do I name the summary tab in excel.  The tab are named based on the date and term selected.  I have the code of what I want the summary to do.  I really just need to figure out how to name the summary because the user may choose some many or one date and term.  Any suggestions
Imports System.Data
Imports System.Data.SqlClient
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Private Const connectionString As String = "Data Source=mddbsqlpfqa.loe.corp; Database=PowerFaids; " _
& "Trusted_Connection=Yes;"

    Dim increment As Integer = 0


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DTPTerm1.Format = DateTimePickerFormat.Custom
        DTPTerm1.CustomFormat = " "
        'Setting up Progress Bar
        If increment > ProgressBar1.Maximum Then
            increment = ProgressBar1.Maximum
        End If
        ProgressBar1.Value = increment
    End Sub


    Private Sub DTPTerm1_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DTPTerm1.ValueChanged
        DTPTerm1.CustomFormat = "MM/dd/yyyy"
    End Sub

    Private Sub btnAddTermStart_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddTermStart.Click
        RichTextBox1.SelectionStart = RichTextBox1.TextLength
        RichTextBox1.SelectedText = DTPTerm1.Value.ToString("MM/dd/yyyy") & " " & COBTermType.SelectedItem & vbCrLf
    End Sub

    Private Sub btnClearTermStarts_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearTermStarts.Click
        RichTextBox1.Clear()
        ProgressBar1.Value = increment
    End Sub

    Private Sub RichTextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RichTextBox1.TextChanged
        
    End Sub

    Private Sub CreateWorksheet(ByRef xlSheet As Excel.Worksheet, ByVal termStartDate As Date, ByVal termTypeFull As String, ByVal tableStyle As String)

        xlSheet.Name = termStartDate.ToShortDateString.Replace("/", "-") & " " & termTypeFull
        xlSheet.Activate()

        Using cnn As New SqlConnection(connectionString)
            cnn.Open()

            'Run SQL'
            Using mysda As New SqlDataAdapter()

                Using mycmd As New SqlCommand(My.Resources.SQL_new_student_metric, cnn)

                    mycmd.CommandTimeout = 0
                    'mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
                    'mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull.Substring(0, 1))
                    mycmd.Parameters.AddWithValue("@PROGRAM_START", termStartDate)
                    mycmd.Parameters.AddWithValue("@TERM_TYPE", termTypeFull)
                    mysda.SelectCommand = mycmd

                    Using ds As New DataSet

                        mysda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet.Cells(1, i + 1) = ds.Tables(0).Columns(i).ToString()
                        Next
                        For i = 0 To ds.Tables(0).Rows.Count - 1
                            For j = 0 To ds.Tables(0).Columns.Count - 1
                                xlSheet.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

                        xlSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                        xlSheet.ListObjects("Table1").TableStyle = tableStyle

                        '~~> Create Borders
                        With xlSheet.UsedRange

                            .Range("O:O").Cells.NumberFormat = "$  ###,###.00"

                            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                                .LineStyle = Excel.XlLineStyle.xlDouble
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With

                            With .Borders(Excel.XlBordersIndex.xlEdgeTop)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlEdgeBottom)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlEdgeRight)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlInsideVertical)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                            With .Borders(Excel.XlBordersIndex.xlInsideHorizontal)
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With
                        End With

                        xlSheet.Columns.AutoFit()

                    End Using
                End Using

            End Using

        End Using
    End Sub

    Private Function ValidateCheck() As Boolean
        If DTPTerm1.CustomFormat = " " Then
            MessageBox.Show("Please select a date.")
            Return False
        ElseIf COBTermType.SelectedIndex = -1 Then
            MessageBox.Show("Please select a term type.")
            COBTermType.Focus()
            Return False

        End If
        Return True
    End Function

    Private Sub btnCreateReport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateReport.Click

        If ValidateCheck() = False Then
            Exit Sub
        End If

        ProgressBar1.Value = increment + 5

        Dim xlApp As New Excel.Application
        Dim xlWorkBook = xlApp.Workbooks
        Dim xlBook = xlWorkBook.Add
        Dim xlSheets = xlBook.Worksheets
        Dim misValue As Object = System.Reflection.Missing.Value

        Do While xlSheets.Count > 1
            Dim delSht = CType(xlSheets.Item(xlSheets.Count - 1), Excel.Worksheet)
            delSht.Delete()
        Loop

        Dim shtCount = 1

        For Each line In RichTextBox1.Lines
            If line.Length > 0 Then
                Dim whiteSpace = line.IndexOf(" "c)
                Dim termDate = line.Substring(0, whiteSpace).Replace("/", "-")
                Dim termType = line.Substring(whiteSpace + 1, 1)

                If shtCount > 1 Then
                    xlSheets.Add(, xlSheets.Item(shtCount - 1))
                End If

                CreateWorksheet(xlSheets.Item(shtCount), termDate, termType, "TableStyleMedium1")
                ProgressBar1.Value = increment + 15
                shtCount += 1
            End If
        Next

Open in new window

0
Comment
Question by:cmed
  • 3
  • 2
5 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40502973
I've no idea what your question is.

However, I would suggest replacing lines 87-123 with:

                            With .Borders()
                                .LineStyle = Excel.XlLineStyle.xlContinuous
                                .ColorIndex = 0
                                .TintAndShade = 0
                                .Weight = Excel.XlBorderWeight.xlThin
                            End With

                            With .Borders(Excel.XlBordersIndex.xlEdgeLeft)
                                .LineStyle = Excel.XlLineStyle.xlDouble
                            End With

Open in new window


It does the same thing, with much fewer lines.
0
 

Author Comment

by:cmed
ID: 40502986
@Phillip Burton

Thanks.  This is the issue that I am having.  I am creating report where I want the user to pick as many terms and date as they need.  Once the user chooses their dates, the report will generate data in separate tabs for each selected date. Each tab will be name the date that the user chooses.  After the user finish choosing their dates the I am trying to get the code to create a separate tab named summary.  I create the program before, I hard coded the dates, so that is why I am making changes.  Does this help understand my question more?
0
 

Author Comment

by:cmed
ID: 40503020
When i hard coded the dates the summary would format the following (see code).  termstart1 and so forth were the dates that was preset. I want to replace those dates with the dates the user choose and repeat the each time the user choose a date. Any suggestion.  I apologize in advance.  I am new to programming.





 With xlSheet7

            .Range("B3").Value = termStart1
            .Range("F3").Value = termStart2
            .Range("J3").Value = termStart3
            .Range("B22").Value = termStart4
            .Range("F22").Value = termStart5
            .Range("J22").Value = termStart6
            .Range("B4, F4, J4, B23, F23, J23").Value = "Status"
            .Range("B5, F5, J5, B24, F24, J24").Value = "Incomplete"
            .Range("B6, F6, J6, B25, F25, J25").Value = "Ready To Package - Special Processing"
            .Range("B7, F7, J7, B26, F26, J26").Value = "Ready To Package"
            .Range("B8, F8, J8, B27, F27, J27").Value = "Awarded"
            .Range("B9, F9, J9, B28, F28, J28").Value = "Declined Aid"
            .Range("B10, F10, J10, B29, F29, J29").Value = "Not Eligible"
            .Range("B11, F11, J11, B30, F30, J30").Value = "Inactive Awarded"
            .Range("B12, F12, J12, B31, F31, J31").Value = "Inactive Not Awarded"
            .Range("B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39").Value = "Total"
            .Range("B15, F15, J15, B34, F34, J34").Value = "Active Students Days RP"
            .Range("B16, F16, J16, B35, F35, J35").Value = "0 - 7"
            .Range("B17, F17, J17, B36, F36, J36").Value = "'8 - 14"
            .Range("B18, F18, J18, B37, F37, J37").Value = "15 - 21"
            .Range("B19, F19, J19, B38, F38, J38").Value = "22+"
            .Range("C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34").Value = "Students"
            .Range("D4, H4, L4, D23, H23, L23, D15, H15, L15, D34, H34, L34").Value = "Percent"

            .Range("C5").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""IP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart1 & "'!$D:$D,""ID"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS""))"
            .Range("C6").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DM"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AW"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C7").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""RP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C8").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C9").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C10").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DS"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""HL"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""NA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""RD"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""SC"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AR"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C11").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""IS"")"
            .Range("C12").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""IS"")"
            .Range("C13").Formula = "=SUM(C5:C12)"
            .Range("C16").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=0"",'" & termStart1 & "'!$F:$F,""<8"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C17").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=8"",'" & termStart1 & "'!$F:$F,""<15"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C18").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=15"",'" & termStart1 & "'!$F:$F,""<22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C19").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C20").Formula = "=SUM(C16:C19)"

            .Range("G5").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""IP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart2 & "'!$D:$D,""ID"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS""))"
            .Range("G6").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DM"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AW"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G7").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""RP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G8").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G9").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G10").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DS"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""HL"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""NA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""RD"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""SC"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AR"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G11").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""IS"")"
            .Range("G12").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""IS"")"
            .Range("G13").Formula = "=SUM(G5:G12)"
            .Range("G16").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=0"",'" & termStart2 & "'!$F:$F,""<8"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G17").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=8"",'" & termStart2 & "'!$F:$F,""<15"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G18").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=15"",'" & termStart2 & "'!$F:$F,""<22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G19").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G20").Formula = "=SUM(G16:G19)"

            .Range("K5").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""IP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart3 & "'!$D:$D,""ID"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS""))"
            .Range("K6").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DM"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AW"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K7").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""RP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K8").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K9").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K10").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DS"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""HL"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""NA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""RD"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""SC"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AR"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K11").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""IS"")"
            .Range("K12").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""IS"")"
            .Range("K13").Formula = "=SUM(K5:K12)"
            .Range("K16").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=0"",'" & termStart3 & "'!$F:$F,""<8"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K17").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=8"",'" & termStart3 & "'!$F:$F,""<15"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K18").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=15"",'" & termStart3 & "'!$F:$F,""<22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K19").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K20").Formula = "=SUM(K16:K19)"

            .Range("C24").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""IP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart4 & "'!$D:$D,""ID"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS""))"
            .Range("C25").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DM"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AW"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C26").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""RP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C27").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C28").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C29").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DS"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""HL"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""NA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""RD"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""SC"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AR"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C30").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""IS"")"
            .Range("C31").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""IS"")"
            .Range("C32").Formula = "=SUM(C24:C31)"
            .Range("C35").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=0"",'" & termStart4 & "'!$F:$F,""<8"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C36").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=8"",'" & termStart4 & "'!$F:$F,""<15"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C37").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=15"",'" & termStart4 & "'!$F:$F,""<22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C38").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C39").Formula = "=SUM(C35:C38)"

            .Range("G24").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""IP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart5 & "'!$D:$D,""ID"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS""))"
            .Range("G25").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DM"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AW"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G26").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""RP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G27").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G28").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G29").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DS"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""HL"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""NA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""RD"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""SC"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AR"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G30").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""IS"")"
            .Range("G31").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""IS"")"
            .Range("G32").Formula = "=SUM(G24:G31)"
            .Range("G35").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=0"",'" & termStart5 & "'!$F:$F,""<8"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G36").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=8"",'" & termStart5 & "'!$F:$F,""<15"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G37").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=15"",'" & termStart5 & "'!$F:$F,""<22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G38").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G39").Formula = "=SUM(G35:G38)"

            .Range("K24").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""IP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart6 & "'!$D:$D,""ID"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS""))"
            .Range("K25").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DM"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AW"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K26").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""RP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K27").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K28").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K29").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DS"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""HL"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""NA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""RD"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""SC"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AR"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K30").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""IS"")"
            .Range("K31").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""IS"")"
            .Range("K32").Formula = "=SUM(K24:K31)"
            .Range("K35").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=0"",'" & termStart6 & "'!$F:$F,""<8"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K36").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=8"",'" & termStart6 & "'!$F:$F,""<15"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K37").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=15"",'" & termStart6 & "'!$F:$F,""<22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K38").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K39").Formula = "=SUM(K35:K38)"

            .Range("D5").Formula = "=C5/C$13"
            .Range("D6").Formula = "=C6/C$13"
            .Range("D7").Formula = "=C7/C$13"
            .Range("D8").Formula = "=C8/C$13"
            .Range("D9").Formula = "=C9/C$13"
            .Range("D10").Formula = "=C10/C$13"
            .Range("D11").Formula = "=C11/C$13"
            .Range("D12").Formula = "=C12/C$13"
            .Range("D16").Formula = "=C16/C$20"
            .Range("D17").Formula = "=C17/C$20"
            .Range("D18").Formula = "=C18/C$20"
            .Range("D19").Formula = "=C19/C$20"

            .Range("H5").Formula = "=G5/G$13"
            .Range("H6").Formula = "=G6/G$13"
            .Range("H7").Formula = "=G7/G$13"
            .Range("H8").Formula = "=G8/G$13"
            .Range("H9").Formula = "=G9/G$13"
            .Range("H10").Formula = "=G10/G$13"
            .Range("H11").Formula = "=G11/G$13"
            .Range("H12").Formula = "=G12/G$13"
            .Range("H16").Formula = "=G16/G$20"
            .Range("H17").Formula = "=G17/G$20"
            .Range("H18").Formula = "=G18/G$20"
            .Range("H19").Formula = "=G19/G$20"

            .Range("L5").Formula = "=K5/K$13"
            .Range("L6").Formula = "=K6/K$13"
            .Range("L7").Formula = "=K7/K$13"
            .Range("L8").Formula = "=K8/K$13"
            .Range("L9").Formula = "=K9/K$13"
            .Range("L10").Formula = "=K10/K$13"
            .Range("L11").Formula = "=K11/K$13"
            .Range("L12").Formula = "=K12/K$13"
            .Range("L16").Formula = "=K16/K$20"
            .Range("L17").Formula = "=K17/K$20"
            .Range("L18").Formula = "=K18/K$20"
            .Range("L19").Formula = "=K19/K$20"

            .Range("D24").Formula = "=C24/C$32"
            .Range("D25").Formula = "=C25/C$32"
            .Range("D26").Formula = "=C26/C$32"
            .Range("D27").Formula = "=C27/C$32"
            .Range("D28").Formula = "=C28/C$32"
            .Range("D29").Formula = "=C29/C$32"
            .Range("D30").Formula = "=C30/C$32"
            .Range("D31").Formula = "=C31/C$32"
            .Range("D35").Formula = "=C35/C$39"
            .Range("D36").Formula = "=C36/C$39"
            .Range("D37").Formula = "=C37/C$39"
            .Range("D38").Formula = "=C38/C$39"

            .Range("H24").Formula = "=G24/G$32"
            .Range("H25").Formula = "=G25/G$32"
            .Range("H26").Formula = "=G26/G$32"
            .Range("H27").Formula = "=G27/G$32"
            .Range("H28").Formula = "=G28/G$32"
            .Range("H29").Formula = "=G29/G$32"
            .Range("H30").Formula = "=G30/G$32"
            .Range("H31").Formula = "=G31/G$32"
            .Range("H35").Formula = "=G35/G$39"
            .Range("H36").Formula = "=G36/G$39"
            .Range("H37").Formula = "=G37/G$39"
            .Range("H38").Formula = "=G38/G$39"

            .Range("L24").Formula = "=K24/K$32"
            .Range("L25").Formula = "=K25/K$32"
            .Range("L26").Formula = "=K26/K$32"
            .Range("L27").Formula = "=K27/K$32"
            .Range("L28").Formula = "=K28/K$32"
            .Range("L29").Formula = "=K29/K$32"
            .Range("L30").Formula = "=K30/K$32"
            .Range("L31").Formula = "=K31/K$32"
            .Range("L35").Formula = "=K35/K$39"
            .Range("L36").Formula = "=K36/K$39"
            .Range("L37").Formula = "=K37/K$39"
            .Range("L38").Formula = "=K38/K$39"

            .Range("D:D, H:H, L:L").NumberFormat = "0%"

            .Range("B5:D5, F5:H5, J5:L5, B24:D24, F24:H24, J24:L24").Interior.Color = RGB(255, 255, 204)
            .Range("B6:D6, F6:H6, J6:L6, B25:D25, F25:H25, J25:L25").Interior.Color = RGB(204, 255, 102)
            .Range("B7:D7, F7:H7, J7:L7, B26:D26, F26:H26, J26:L26").Interior.Color = RGB(153, 255, 153)
            .Range("B8:D8, F8:H8, J8:L8, B27:D27, F27:H27, J27:L27").Interior.Color = RGB(51, 204, 51)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Interior.Color = RGB(0, 51, 0)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Font.Color = RGB(255, 255, 255)
            .Range("B11:D12, F11:H12, J11:L12, B30:D31, F30:H31, J30:L31").Interior.Color = RGB(191, 191, 191)
            .Range("B16:D19, F16:H19, J16:L19, B35:D38, F35:H38, J35:L38").Interior.Color = RGB(153, 255, 153)

Open in new window

0
 

Author Comment

by:cmed
ID: 40503022
termstart1 and so forth was the name of the sheet which was the date chosen
 With xlSheet7

            .Range("B3").Value = termStart1
            .Range("F3").Value = termStart2
            .Range("J3").Value = termStart3
            .Range("B22").Value = termStart4
            .Range("F22").Value = termStart5
            .Range("J22").Value = termStart6
            .Range("B4, F4, J4, B23, F23, J23").Value = "Status"
            .Range("B5, F5, J5, B24, F24, J24").Value = "Incomplete"
            .Range("B6, F6, J6, B25, F25, J25").Value = "Ready To Package - Special Processing"
            .Range("B7, F7, J7, B26, F26, J26").Value = "Ready To Package"
            .Range("B8, F8, J8, B27, F27, J27").Value = "Awarded"
            .Range("B9, F9, J9, B28, F28, J28").Value = "Declined Aid"
            .Range("B10, F10, J10, B29, F29, J29").Value = "Not Eligible"
            .Range("B11, F11, J11, B30, F30, J30").Value = "Inactive Awarded"
            .Range("B12, F12, J12, B31, F31, J31").Value = "Inactive Not Awarded"
            .Range("B13, F13, J13, B32, F32, J32, B20, F20, J20, B39, F39, J39").Value = "Total"
            .Range("B15, F15, J15, B34, F34, J34").Value = "Active Students Days RP"
            .Range("B16, F16, J16, B35, F35, J35").Value = "0 - 7"
            .Range("B17, F17, J17, B36, F36, J36").Value = "'8 - 14"
            .Range("B18, F18, J18, B37, F37, J37").Value = "15 - 21"
            .Range("B19, F19, J19, B38, F38, J38").Value = "22+"
            .Range("C4, G4, K4, C23, G23, K23, C15, G15, K15, C34, G34, K34").Value = "Students"
            .Range("D4, H4, L4, D23, H23, L23, D15, H15, L15, D34, H34, L34").Value = "Percent"

            .Range("C5").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""IP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart1 & "'!$D:$D,""ID"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS""))"
            .Range("C6").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DM"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AW"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C7").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""RP"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C8").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C9").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C10").Formula = "=COUNTIFS('" & termStart1 & "'!$D:$D,""DS"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""HL"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""NA"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""RD"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""SC"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart1 & "'!$D:$D,""AR"",'" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C11").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""Y"",'" & termStart1 & "'!$H:$H,""IS"")"
            .Range("C12").Formula = "=COUNTIFS('" & termStart1 & "'!$E:$E,""N"",'" & termStart1 & "'!$H:$H,""IS"")"
            .Range("C13").Formula = "=SUM(C5:C12)"
            .Range("C16").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=0"",'" & termStart1 & "'!$F:$F,""<8"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C17").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=8"",'" & termStart1 & "'!$F:$F,""<15"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C18").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=15"",'" & termStart1 & "'!$F:$F,""<22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C19").Formula = "=COUNTIFS('" & termStart1 & "'!$F:$F,"">=22"",'" & termStart1 & "'!$H:$H,""<>IS"")"
            .Range("C20").Formula = "=SUM(C16:C19)"

            .Range("G5").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""IP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart2 & "'!$D:$D,""ID"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS""))"
            .Range("G6").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DM"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AW"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G7").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""RP"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G8").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G9").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G10").Formula = "=COUNTIFS('" & termStart2 & "'!$D:$D,""DS"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""HL"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""NA"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""RD"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""SC"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart2 & "'!$D:$D,""AR"",'" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G11").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""Y"",'" & termStart2 & "'!$H:$H,""IS"")"
            .Range("G12").Formula = "=COUNTIFS('" & termStart2 & "'!$E:$E,""N"",'" & termStart2 & "'!$H:$H,""IS"")"
            .Range("G13").Formula = "=SUM(G5:G12)"
            .Range("G16").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=0"",'" & termStart2 & "'!$F:$F,""<8"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G17").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=8"",'" & termStart2 & "'!$F:$F,""<15"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G18").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=15"",'" & termStart2 & "'!$F:$F,""<22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G19").Formula = "=COUNTIFS('" & termStart2 & "'!$F:$F,"">=22"",'" & termStart2 & "'!$H:$H,""<>IS"")"
            .Range("G20").Formula = "=SUM(G16:G19)"

            .Range("K5").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""IP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart3 & "'!$D:$D,""ID"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS""))"
            .Range("K6").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DM"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AW"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K7").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""RP"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K8").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K9").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K10").Formula = "=COUNTIFS('" & termStart3 & "'!$D:$D,""DS"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""HL"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""NA"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""RD"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""SC"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart3 & "'!$D:$D,""AR"",'" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K11").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""Y"",'" & termStart3 & "'!$H:$H,""IS"")"
            .Range("K12").Formula = "=COUNTIFS('" & termStart3 & "'!$E:$E,""N"",'" & termStart3 & "'!$H:$H,""IS"")"
            .Range("K13").Formula = "=SUM(K5:K12)"
            .Range("K16").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=0"",'" & termStart3 & "'!$F:$F,""<8"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K17").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=8"",'" & termStart3 & "'!$F:$F,""<15"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K18").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=15"",'" & termStart3 & "'!$F:$F,""<22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K19").Formula = "=COUNTIFS('" & termStart3 & "'!$F:$F,"">=22"",'" & termStart3 & "'!$H:$H,""<>IS"")"
            .Range("K20").Formula = "=SUM(K16:K19)"

            .Range("C24").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""IP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart4 & "'!$D:$D,""ID"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS""))"
            .Range("C25").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DM"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AW"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C26").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""RP"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C27").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C28").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C29").Formula = "=COUNTIFS('" & termStart4 & "'!$D:$D,""DS"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""HL"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""NA"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""RD"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""SC"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart4 & "'!$D:$D,""AR"",'" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C30").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""Y"",'" & termStart4 & "'!$H:$H,""IS"")"
            .Range("C31").Formula = "=COUNTIFS('" & termStart4 & "'!$E:$E,""N"",'" & termStart4 & "'!$H:$H,""IS"")"
            .Range("C32").Formula = "=SUM(C24:C31)"
            .Range("C35").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=0"",'" & termStart4 & "'!$F:$F,""<8"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C36").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=8"",'" & termStart4 & "'!$F:$F,""<15"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C37").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=15"",'" & termStart4 & "'!$F:$F,""<22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C38").Formula = "=COUNTIFS('" & termStart4 & "'!$F:$F,"">=22"",'" & termStart4 & "'!$H:$H,""<>IS"")"
            .Range("C39").Formula = "=SUM(C35:C38)"

            .Range("G24").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""IP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart5 & "'!$D:$D,""ID"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS""))"
            .Range("G25").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DM"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AW"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G26").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""RP"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G27").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G28").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G29").Formula = "=COUNTIFS('" & termStart5 & "'!$D:$D,""DS"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""HL"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""NA"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""RD"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""SC"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart5 & "'!$D:$D,""AR"",'" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G30").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""Y"",'" & termStart5 & "'!$H:$H,""IS"")"
            .Range("G31").Formula = "=COUNTIFS('" & termStart5 & "'!$E:$E,""N"",'" & termStart5 & "'!$H:$H,""IS"")"
            .Range("G32").Formula = "=SUM(G24:G31)"
            .Range("G35").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=0"",'" & termStart5 & "'!$F:$F,""<8"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G36").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=8"",'" & termStart5 & "'!$F:$F,""<15"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G37").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=15"",'" & termStart5 & "'!$F:$F,""<22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G38").Formula = "=COUNTIFS('" & termStart5 & "'!$F:$F,"">=22"",'" & termStart5 & "'!$H:$H,""<>IS"")"
            .Range("G39").Formula = "=SUM(G35:G38)"

            .Range("K24").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""IP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+(COUNTIFS('" & termStart6 & "'!$D:$D,""ID"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS""))"
            .Range("K25").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DM"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AW"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K26").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""RP"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K27").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K28").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K29").Formula = "=COUNTIFS('" & termStart6 & "'!$D:$D,""DS"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""HL"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""NA"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""RD"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""SC"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")+COUNTIFS('" & termStart6 & "'!$D:$D,""AR"",'" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K30").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""Y"",'" & termStart6 & "'!$H:$H,""IS"")"
            .Range("K31").Formula = "=COUNTIFS('" & termStart6 & "'!$E:$E,""N"",'" & termStart6 & "'!$H:$H,""IS"")"
            .Range("K32").Formula = "=SUM(K24:K31)"
            .Range("K35").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=0"",'" & termStart6 & "'!$F:$F,""<8"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K36").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=8"",'" & termStart6 & "'!$F:$F,""<15"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K37").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=15"",'" & termStart6 & "'!$F:$F,""<22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K38").Formula = "=COUNTIFS('" & termStart6 & "'!$F:$F,"">=22"",'" & termStart6 & "'!$H:$H,""<>IS"")"
            .Range("K39").Formula = "=SUM(K35:K38)"

            .Range("D5").Formula = "=C5/C$13"
            .Range("D6").Formula = "=C6/C$13"
            .Range("D7").Formula = "=C7/C$13"
            .Range("D8").Formula = "=C8/C$13"
            .Range("D9").Formula = "=C9/C$13"
            .Range("D10").Formula = "=C10/C$13"
            .Range("D11").Formula = "=C11/C$13"
            .Range("D12").Formula = "=C12/C$13"
            .Range("D16").Formula = "=C16/C$20"
            .Range("D17").Formula = "=C17/C$20"
            .Range("D18").Formula = "=C18/C$20"
            .Range("D19").Formula = "=C19/C$20"

            .Range("H5").Formula = "=G5/G$13"
            .Range("H6").Formula = "=G6/G$13"
            .Range("H7").Formula = "=G7/G$13"
            .Range("H8").Formula = "=G8/G$13"
            .Range("H9").Formula = "=G9/G$13"
            .Range("H10").Formula = "=G10/G$13"
            .Range("H11").Formula = "=G11/G$13"
            .Range("H12").Formula = "=G12/G$13"
            .Range("H16").Formula = "=G16/G$20"
            .Range("H17").Formula = "=G17/G$20"
            .Range("H18").Formula = "=G18/G$20"
            .Range("H19").Formula = "=G19/G$20"

            .Range("L5").Formula = "=K5/K$13"
            .Range("L6").Formula = "=K6/K$13"
            .Range("L7").Formula = "=K7/K$13"
            .Range("L8").Formula = "=K8/K$13"
            .Range("L9").Formula = "=K9/K$13"
            .Range("L10").Formula = "=K10/K$13"
            .Range("L11").Formula = "=K11/K$13"
            .Range("L12").Formula = "=K12/K$13"
            .Range("L16").Formula = "=K16/K$20"
            .Range("L17").Formula = "=K17/K$20"
            .Range("L18").Formula = "=K18/K$20"
            .Range("L19").Formula = "=K19/K$20"

            .Range("D24").Formula = "=C24/C$32"
            .Range("D25").Formula = "=C25/C$32"
            .Range("D26").Formula = "=C26/C$32"
            .Range("D27").Formula = "=C27/C$32"
            .Range("D28").Formula = "=C28/C$32"
            .Range("D29").Formula = "=C29/C$32"
            .Range("D30").Formula = "=C30/C$32"
            .Range("D31").Formula = "=C31/C$32"
            .Range("D35").Formula = "=C35/C$39"
            .Range("D36").Formula = "=C36/C$39"
            .Range("D37").Formula = "=C37/C$39"
            .Range("D38").Formula = "=C38/C$39"

            .Range("H24").Formula = "=G24/G$32"
            .Range("H25").Formula = "=G25/G$32"
            .Range("H26").Formula = "=G26/G$32"
            .Range("H27").Formula = "=G27/G$32"
            .Range("H28").Formula = "=G28/G$32"
            .Range("H29").Formula = "=G29/G$32"
            .Range("H30").Formula = "=G30/G$32"
            .Range("H31").Formula = "=G31/G$32"
            .Range("H35").Formula = "=G35/G$39"
            .Range("H36").Formula = "=G36/G$39"
            .Range("H37").Formula = "=G37/G$39"
            .Range("H38").Formula = "=G38/G$39"

            .Range("L24").Formula = "=K24/K$32"
            .Range("L25").Formula = "=K25/K$32"
            .Range("L26").Formula = "=K26/K$32"
            .Range("L27").Formula = "=K27/K$32"
            .Range("L28").Formula = "=K28/K$32"
            .Range("L29").Formula = "=K29/K$32"
            .Range("L30").Formula = "=K30/K$32"
            .Range("L31").Formula = "=K31/K$32"
            .Range("L35").Formula = "=K35/K$39"
            .Range("L36").Formula = "=K36/K$39"
            .Range("L37").Formula = "=K37/K$39"
            .Range("L38").Formula = "=K38/K$39"

            .Range("D:D, H:H, L:L").NumberFormat = "0%"

            .Range("B5:D5, F5:H5, J5:L5, B24:D24, F24:H24, J24:L24").Interior.Color = RGB(255, 255, 204)
            .Range("B6:D6, F6:H6, J6:L6, B25:D25, F25:H25, J25:L25").Interior.Color = RGB(204, 255, 102)
            .Range("B7:D7, F7:H7, J7:L7, B26:D26, F26:H26, J26:L26").Interior.Color = RGB(153, 255, 153)
            .Range("B8:D8, F8:H8, J8:L8, B27:D27, F27:H27, J27:L27").Interior.Color = RGB(51, 204, 51)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Interior.Color = RGB(0, 51, 0)
            .Range("B9:D10, F9:H10, J9:L10, B28:D29, F28:H29, J28:L29").Font.Color = RGB(255, 255, 255)
            .Range("B11:D12, F11:H12, J11:L12, B30:D31, F30:H31, J30:L31").Interior.Color = RGB(191, 191, 191)
            .Range("B16:D19, F16:H19, J16:L19, B35:D38, F35:H38, J35:L38").Interior.Color = RGB(153, 255, 153)

Open in new window

0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 500 total points
ID: 40506658
>> termstart1 and so forth were the dates that was preset. I want to replace those dates with the dates the user choose and repeat the each time the user choose a date.

Have those dates in separate cells, and then read them like:
termstart1 = Sheets("InputSheet").cells(1,1)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now