Solved

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

Posted on 2014-11-21
5
124 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
Comment Utility
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
Comment Utility
I did and it is asking to update instead of performing the formula.
0
 
LVL 24

Expert Comment

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

Accepted Solution

by:
Phillip Burton earned 500 total points
Comment Utility
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
Comment Utility
I caught before I checked this message and now I am running again. I will keep you updated.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
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…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

762 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

9 Experts available now in Live!

Get 1:1 Help Now