?
Solved

vb.net countif issues

Posted on 2014-11-20
3
Medium Priority
?
278 Views
Last Modified: 2014-11-20
i am having an issue with trying to code a countif formula and i am getting this err
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('09-02-14 S'!$D:$D,"IP",'09-02-14 S'!$E:$E,"N",'09-02-14 S'!$H:$H,"<>IS")+(COUNTIFS('09-02-14 S'!$D:$D,"ID",'09-02-14 S'!$E:$E,"N",'09-02-14 S'!$H:$H,"<>IS"))"




            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

or message "end of statement"error message from code
0
Comment
Question by:cmed
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 40456071
You need to use double double quotes when contained within a string variable....

.Range("C5").Formula = "=COUNTIFS('09-02-14 S'!$D:$D,""IP"",'09-02-14 S'!$E:$E,""N"",'09-02-14 S'!$H:$H,""<>IS"")+(COUNTIFS('09-02-14 S'!$D:$D,""ID"",'09-02-14 S'!$E:$E,""N"",'09-02-14 S'!$H:$H,""<>IS""))"

Open in new window

0
 

Author Comment

by:cmed
ID: 40456114
Thanks. It worked.
0
 

Author Closing Comment

by:cmed
ID: 40456224
Thank you. It worked
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month14 days, 18 hours left to enroll

770 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