Solved

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

Posted on 2014-11-21
5
137 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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 …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

828 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