Solved

How to create excel countifs from another worksheet.  Code is not working

Posted on 2014-11-21
5
132 Views
Last Modified: 2014-11-21
I have a code that is running fine until I get to 590 and 591. The formula that i am using will not populate the correct information from the sheet as when I do the same formula from a regular excel spreadsheet.  
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;"
    Private Sub btnGenerate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerate.Click

        Dim i, j As Integer

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlSheet6 As Excel.Worksheet
        Dim xlSheet5 As Excel.Worksheet
        Dim xlSheet4 As Excel.Worksheet
        Dim xlSheet1 As Excel.Worksheet
        Dim xlSheet2 As Excel.Worksheet
        Dim xlSheet3 As Excel.Worksheet
        Dim xlSheet7 As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value




        xlApp = New Excel.ApplicationClass
        xlWorkBook = xlApp.Workbooks.Add(misValue)


        xlWorkBook.Sheets.Add(, , 4)


        xlSheet6 = CType(xlWorkBook.Worksheets("Sheet6"), Excel.Worksheet)
        xlSheet6.Activate()
        xlSheet6.Name = "9-2-2014 S"
        xlSheet6.Tab.ColorIndex = CType(3, Excel.XlColorIndex)


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

            'Run SQL'
            Using sheet6sda As New SqlDataAdapter()

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

                    sheet6cmd.CommandTimeout = 0
                    sheet6cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
                    sheet6cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet6sda.SelectCommand = sheet6cmd

                    Using ds As New DataSet

                        sheet6sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet6.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
                                xlSheet6.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

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

                        '~~> Create Borders
                        With xlSheet6.UsedRange

                            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

                        xlSheet6.Columns.AutoFit()
                    End Using

                End Using

            End Using

            ''NAME EXCEL SHEET 2''
            xlSheet5 = CType(xlWorkBook.Worksheets("Sheet5"), Excel.Worksheet)
            xlSheet5.Activate()
            xlSheet5.Name = "9-2-2014 Q"
            xlSheet5.Tab.ColorIndex = CType(6, Excel.XlColorIndex)

            'Run SQL'
            Using sheet5sda As New SqlDataAdapter()

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

                    sheet5cmd.CommandTimeout = 0
                    sheet5cmd.Parameters.AddWithValue("@PROGRAM_START", "9/2/2014")
                    sheet5cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet5sda.SelectCommand = sheet5cmd

                    Using ds As New DataSet

                        sheet5sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet5.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
                                xlSheet5.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next



                        'Format Table'

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

                        '~~> Create Borders
                        With xlSheet5.UsedRange

                            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

                        xlSheet5.Columns.AutoFit()
                    End Using
                End Using
            End Using



            ''NAME EXCEL SHEET 3''
            xlSheet4 = CType(xlWorkBook.Worksheets("Sheet4"), Excel.Worksheet)
            xlSheet4.Activate()
            xlSheet4.Name = "10-13-2014 Q"
            xlSheet4.Tab.ColorIndex = CType(9, Excel.XlColorIndex)

            'Run SQL'
            Using sheet4sda As New SqlDataAdapter()

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

                    sheet4cmd.CommandTimeout = 0
                    sheet4cmd.Parameters.AddWithValue("@PROGRAM_START", "10/13/2014")
                    sheet4cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet4sda.SelectCommand = sheet4cmd

                    Using ds As New DataSet

                        sheet4sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet4.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
                                xlSheet4.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

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

                        '~~> Create Borders
                        With xlSheet4.UsedRange

                            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

                        xlSheet4.Columns.AutoFit()
                    End Using
                End Using
            End Using


            ''NAME EXCEL SHEET 4''
            xlSheet1 = CType(xlWorkBook.Worksheets("Sheet1"), Excel.Worksheet)
            xlSheet1.Activate()
            xlSheet1.Name = "10-27-2014 S"
            xlSheet1.Tab.ColorIndex = CType(29, Excel.XlColorIndex)

            'Run SQL'
            Using sheet1sda As New SqlDataAdapter()

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

                    sheet1cmd.CommandTimeout = 0
                    sheet1cmd.Parameters.AddWithValue("@PROGRAM_START", "10/27/2014")
                    sheet1cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet1sda.SelectCommand = sheet1cmd

                    Using ds As New DataSet

                        sheet1sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet1.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
                                xlSheet1.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

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

                        'Create Borders
                        With xlSheet1.UsedRange

                            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
                    End Using
                End Using
            End Using


            xlSheet1.Columns.AutoFit()


            ''NAME EXCEL SHEET 5''
            xlSheet2 = CType(xlWorkBook.Worksheets("Sheet2"), Excel.Worksheet)
            xlSheet2.Activate()
            xlSheet2.Name = "12-01-2014 Q"
            xlSheet2.Tab.ColorIndex = CType(12, Excel.XlColorIndex)

            'Run SQL'
            Using sheet2sda As New SqlDataAdapter()

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

                    sheet2cmd.CommandTimeout = 0
                    sheet2cmd.Parameters.AddWithValue("@PROGRAM_START", "12/1/2014")
                    sheet2cmd.Parameters.AddWithValue("@TERM_TYPE", "Q")
                    sheet2sda.SelectCommand = sheet2cmd

                    Using ds As New DataSet

                        sheet2sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet2.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
                                xlSheet2.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next

                        'Format Table'

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

                        '~~> Create Borders
                        With xlSheet2.UsedRange

                            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

                        xlSheet2.Columns.AutoFit()

                    End Using
                End Using
            End Using



            ''NAME EXCEL SHEET 6''
            xlSheet3 = CType(xlWorkBook.Worksheets("Sheet3"), Excel.Worksheet)
            xlSheet3.Activate()
            xlSheet3.Name = "01-05-2015 S"
            xlSheet3.Tab.ColorIndex = CType(22, Excel.XlColorIndex)

            'Run SQL'
            Using sheet3sda As New SqlDataAdapter()

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

                    sheet3cmd.CommandTimeout = 0
                    sheet3cmd.Parameters.AddWithValue("@PROGRAM_START", "1/5/2015")
                    sheet3cmd.Parameters.AddWithValue("@TERM_TYPE", "S")
                    sheet3sda.SelectCommand = sheet3cmd

                    Using ds As New DataSet

                        sheet3sda.Fill(ds)

                        'Display Headers, Columns, and Rows'

                        For i = 0 To ds.Tables(0).Columns.Count - 1
                            xlSheet3.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
                                xlSheet3.Cells(i + 2, j + 1) = _
                                ds.Tables(0).Rows(i).Item(j)
                            Next
                        Next



                        'Format Table'

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

                        '~~> Create Borders
                        With xlSheet3.UsedRange

                            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

                        xlSheet3.Columns.AutoFit()
                    End Using
                End Using
            End Using



            ''NAME EXCEL SHEET 7''
            xlSheet7 = CType(xlWorkBook.Worksheets("Sheet7"), Excel.Worksheet)
            xlSheet7.Activate()
            xlSheet7.Name = "Summary"
            xlSheet7.Tab.ColorIndex = CType(14, Excel.XlColorIndex)




            With xlSheet7

                .Range("A1").Value = "Summary of ISIRs Received for Admitted Students"

                .Range("B3").Value = "9/2/2014 S"
                .Range("B4").Value = "Status"
                .Range("B5").Value = "Incomplete"
                .Range("B6").Value = "Ready To Package - Special Processing"
                .Range("B7").Value = "Ready To Package"
                .Range("B8").Value = "Awarded"
                .Range("B9").Value = "Declined Aid"
                .Range("B10").Value = "Not Eligible"
                .Range("B11").Value = "Inactive Awarded"
                .Range("B12").Value = "Inactive Not Awarded"
                .Range("B13").Value = "Total"
                .Range("B15").Value = "Active Students Days RP"
                .Range("B16").Value = "0 - 7"
                .Range("B17").Value = "8 - 14"
                .Range("B18").Value = "15 - 21"
                .Range("B19").Value = "22+"
                .Range("B20").Value = "Total"

                .Range("C4").Value = "Students"
                .Range("C5").Formula = "=COUNTIFS('9-2-2014 S'!$D:$D,""IP"",'09-02-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")+(COUNTIFS('9-2-2014 S'!$D:$D,""ID"",'9-2-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS""))"
                .Range("C6").Formula = "=COUNTIFS('9-2-2014 S'!$D:$D,""DM"",'09-02-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")+COUNTIFS('9-2-2014 S'!$D:$D,""AW"",'9-2-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")"

                'Format Table'

                'xlSheet7.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, xlSheet7.UsedRange, , Excel.XlYesNoGuess.xlYes).Name = "Table1"
                'xlSheet7.ListObjects("Table1").TableStyle = "TableStyleMedium9"

                '~~> Create Borders
                With xlSheet7.UsedRange

                    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

                xlSheet7.Columns.AutoFit()

            End With

            xlApp.Visible = True

        End Using


        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlSheet6)
        releaseObject(xlSheet5)
        releaseObject(xlSheet4)
        releaseObject(xlSheet1)
        releaseObject(xlSheet2)
        releaseObject(xlSheet3)
        releaseObject(xlSheet7)

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Open in new window


The lines in questions are:

 .Range("C5").Formula = "=COUNTIFS('9-2-2014 S'!$D:$D,""IP"",'09-02-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")+(COUNTIFS('9-2-2014 S'!$D:$D,""ID"",'9-2-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS""))"
                .Range("C6").Formula = "=COUNTIFS('9-2-2014 S'!$D:$D,""DM"",'09-02-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")+COUNTIFS('9-2-2014 S'!$D:$D,""AW"",'9-2-2014 S'!$E:$E,""N"",'9-2-2014 S'!$H:$H,""<>IS"")"
0
Comment
Question by:cmed
  • 3
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40457409
Looks fine to me - what does it do instead?

Have you tried putting a breakpoint on line 590 and seeing what happens line by line?
0
 

Author Comment

by:cmed
ID: 40457484
I did and it is asking to update instead of performing the formula.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40457502
Is there a spreadsheet called "9-2-2014 S" inside that workbook?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40457503
Got it - you also refer to "09-02-2014 S" - note that there is an extra "0" at the front of that name!
0
 

Author Comment

by:cmed
ID: 40457571
I caught before I checked this message and now I am running again. I will keep you updated.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

776 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