option strict on disallows late binding

Try this code you are missing the setting of oWS as a worksheet and also cannot use Nothing but vbEmpty instead like below.

Sub test()
Dim BlankCell As Excel.Range
Set oWS = ActiveSheet

 '<---Find blank cells--->'
        Set BlankCell = oWS.Columns(1).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(2).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(3).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(4).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(5).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
        Set BlankCell = oWS.Columns(6).Find(vbEmpty, LookIn:=xlValues, LookAt:=xlWhole)
End Sub

Select allOpen in new window

gowflow

And here is an other way of doing it

Sub FindBlanks()
Dim BlankCell As Range
Dim WS As Worksheet

Set WS = ActiveSheet

For Each BlankCell In WS.UsedRange.SpecialCells(xlCellTypeBlanks)
    Debug.Print BlankCell.Address
Next BlankCell
End Sub

Select allOpen in new window

This will list all addresses of blank cells in the usedrange of the current worksheet.

gowflow

@gowflow

when i replace my code with yours, it still has the same error message.

Here is the entire original code

Option Explicit On
Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

    Private Const fileName = "C:\read file\EA report.xlsx"
    'Private Const txtfileName = "C:\read file\EA report.txt"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtpTermStart.Format = DateTimePickerFormat.Custom
        dtpTermStart.CustomFormat = " "
        dtpTermEnd.Format = DateTimePickerFormat.Custom
        dtpTermEnd.CustomFormat = " "
        dtpFirstDayInReport.Format = DateTimePickerFormat.Custom
        dtpFirstDayInReport.CustomFormat = " "
        dtpLastDayInReport.Format = DateTimePickerFormat.Custom
        dtpLastDayInReport.CustomFormat = " "
    End Sub

    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged
        dtpTermStart.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker2_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermEnd.ValueChanged
        dtpTermEnd.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker3_ValueChanged(sender As Object, e As EventArgs) Handles dtpFirstDayInReport.ValueChanged
        dtpFirstDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker4_ValueChanged(sender As Object, e As EventArgs) Handles dtpLastDayInReport.ValueChanged
        dtpLastDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Function Error_check() As Boolean
        If dtpFirstDayInReport.Value.Date >= dtpLastDayInReport.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        ElseIf dtpTermStart.Value.Date >= dtpTermEnd.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        
        End If
        Return True
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim oApp As New Excel.Application
        Dim oWBk As Excel.Workbook = oApp.Workbooks.Open(fileName)
        Dim oWS As Excel.Worksheet = DirectCast(oWBk.Worksheets(1), Excel.Worksheet)
        Const xlvalues As Long = -4163
        Const xlWhole = 1

        Dim BlankCell As Excel.Range

        If Error_check() = False Then
            Exit Sub
        End If

        '<---Find blank cells--->'
        BlankCell = oWS.Columns(1).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(2).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(3).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(4).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(5).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = oWS.Columns(6).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

        oWS.Range(String.Format("B{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yy} {1}", dtpTermStart.Value.Date, cbTermType.SelectedItem.ToString(0))
        oWS.Range(String.Format("B{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("C{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermStart.Value.Date)
        oWS.Range(String.Format("D{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermEnd.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpFirstDayInReport.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpLastDayInReport.Value.Date)
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Font.Bold = True

        oWS.Columns.AutoFit()

        Dim usedRows As Excel.Range = oWS.UsedRange.Rows
        For Each usedRows In oWS.UsedRange.Rows
            For Each cell As Excel.Range In usedRows.Cells
                RichTextBox1.Text &= cell.Value
            Next

            RichTextBox1.Text &= vbNewLine

        Next

        oWS.SaveAs(fileName)
        oWBk.Close()

    End Sub
End Class

Select allOpen in new window

Is this in a module or a in Class Module and I get plenty of errors. Can you post the workbook instead this would save both of us wasted time.

gowflow

well don't see how but if you said it is solved then it is solved. The piece of code you posted shows nothing.
gowflow

instead of coding

 '<---Find blank cells--->'
       BlankCell = oWS.Columns(1).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

I coded

BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

and it works great.

Better than CType, declare your variable as a Worksheet instead of an Object. CType implies a conversion and is slower.

Dim oWS As Excel.Worksheet.

@James Burger

I declared oWS as a worksheet in my original code.  Do you see anywhere else I could tightened this code?  Thanks in advance.  

Option Explicit On
Option Strict On
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1

    Private Const fileName = "C:\read file\EA report.xlsx"
    'Private Const txtfileName = "C:\read file\EA report.txt"

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        dtpTermStart.Format = DateTimePickerFormat.Custom
        dtpTermStart.CustomFormat = " "
        dtpTermEnd.Format = DateTimePickerFormat.Custom
        dtpTermEnd.CustomFormat = " "
        dtpFirstDayInReport.Format = DateTimePickerFormat.Custom
        dtpFirstDayInReport.CustomFormat = " "
        dtpLastDayInReport.Format = DateTimePickerFormat.Custom
        dtpLastDayInReport.CustomFormat = " "

    End Sub

    Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged
        dtpTermStart.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker2_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermEnd.ValueChanged
        dtpTermEnd.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker3_ValueChanged(sender As Object, e As EventArgs) Handles dtpFirstDayInReport.ValueChanged
        dtpFirstDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Sub DateTimePicker4_ValueChanged(sender As Object, e As EventArgs) Handles dtpLastDayInReport.ValueChanged
        dtpLastDayInReport.CustomFormat = "MM/dd/yy"
    End Sub

    Private Function Error_check() As Boolean
        If dtpFirstDayInReport.Value.Date >= dtpLastDayInReport.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        ElseIf dtpTermStart.Value.Date >= dtpTermEnd.Value.Date Then
            MessageBox.Show("Please make sure that the first day in report is before the last day in report!!",
                                "Invalid Date", MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button2)
            Return False
        End If
        Return True
    End Function

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        If Error_check() = False Then
            Exit Sub
        End If

        Dim d As Date
        d = Date.FromOADate(CDbl(Date.Now.ToOADate()))
        Dim oApp As New Excel.Application
        Dim oWBk As Excel.Workbook = oApp.Workbooks.Open(fileName)
        Dim oWS As Excel.Worksheet = DirectCast(oWBk.Worksheets(1), Excel.Worksheet)
        Dim BlankCell As Excel.Range
        Const xlvalues As Long = -4163
        Const xlWhole = 1

        For i = 1 To oWS.UsedRange.Rows.Count
            If IsDate(oWS.Range(String.Format("E{0}", i)).Value) = True Then
                If d > CDate(oWS.Range(String.Format("F{0}", i)).Value) Then
                    CType(oWS.Rows(i), Excel.Range).Delete()
                End If
            End If
        Next

        ''<---Find blank cells--->'
        BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(2), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(3), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(4), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(5), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(6), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)

        oWS.Range(String.Format("B{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yy} {1}", dtpTermStart.Value.Date, cbTermType.SelectedItem.ToString(0))
        oWS.Range(String.Format("B{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("C{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermStart.Value.Date)
        oWS.Range(String.Format("D{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpTermEnd.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpFirstDayInReport.Value.Date)
        oWS.Range(String.Format("E{0}", BlankCell.Row)).Font.Bold = True
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Value = String.Format("{0:M/d/yyyy}", dtpLastDayInReport.Value.Date)
        oWS.Range(String.Format("F{0}", BlankCell.Row)).Font.Bold = True

        oWS.Columns.AutoFit()
        oWS.SaveAs(fileName)
        oWBk.Close()

        '<---VIEW EXCEL FILE--->
        Dim myConnection As System.Data.OleDb.OleDbConnection
        Dim dataSet As System.Data.DataSet
        Dim myCommand As System.Data.OleDb.OleDbDataAdapter
        Dim path As String = fileName

        myConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
        myCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", myConnection)
        dataSet = New System.Data.DataSet
        myCommand.Fill(dataSet)
        DataGridView1.DataSource = dataSet.Tables(0)
        myConnection.Close()
    End Sub
End Class

Select allOpen in new window

Sorry for the incomplete answer, Windows Update crashed my computer while I was writing it. Here is a complete one.

Line 7:
     Does this line compile? Option Strict usually requires that you define the type of constants.
     Private Const fileName As String = "C:\read file\EA report.xlsx"

Line 58:
     You are converting ToOADate and then back FormOADate.
     It's like converting a String to an Integer and then back to a String in the same expression. Useless.
     You do not need to deal with OADate when you are working with Excel through a reference. It's done automatically.
     The following is sufficient:
     d = Date.Now

Line 61:
     No need for the DirectCast, oWBk.Worksheets(1) is already a Worksheet

Lines 75-80:
     Are you trying to find the first column that has a blank cell or any column that has one?
     The way you are doing it, if column 1 has a blank cell and column 3 has a blank cell, you will be left with column 3 as a result.

Lines 75-80:
     I am not sure of that one, but I do not think that looking for Nothing will work. It does not in VBA. Excel cells always have a default value. I would look for an empty string instead.

Line 99:
     You already have the constant, there is no need to assign it to a variable. Simply use the constant.

Lines 103-105
     You have only one table, but use a DataSet. This is a common mistake that is unfortunately repeated everywhere.
     You use a DataSet when you have more than one table, and they are linked together through relations.
     If there is no DataRelation object in the code, there is no need for a DataSet.
     You might want to read my article on the subject.

		Dim dataTable As System.Data.DataTable
		dataTable = New System.Data.DataTable
		myCommand.Fill(dataTable)
		DataGridView1.DataSource = dataTable

Select allOpen in new window

   
When you have 4 event methods that have the same code, simply on different controls, you can combine them into only one procedure:

	Private Sub DateTimePicker1_ValueChanged(sender As Object, e As EventArgs) Handles dtpTermStart.ValueChanged, dtpTermEnd.ValueChanged, dtpFirstDayInReport.ValueChanged, dtpLastDayInReport.ValueChanged
		DirectCast(sender, DateTimePicker).CustomFormat = "MM/dd/yy"
	End Sub

Select allOpen in new window

@James

LIne 7 - This is just the file that I want the dates from the datetime picker to add to the file
Line 58 - This was just me testing different dates by adding or subtracting from today's date.  The final code before production will be  d = Date.Now
Line 75-80- I am trying to find the first blank cell in each column and then add the dates to the first available blank cell.  The file has already been created and I am allowing the user to add new dates to the file.

Everything else I am going to make the necessary adjustments.  Thanks for your knowledge.

I figured it out after doing some reading.  I just use a conversion type (CType) and it worked just fine.  

Dim BlankCell As Excel.Range
   

        ''<---Find blank cells--->'
        BlankCell = CType(oWS.Columns(1), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(2), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(3), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(4), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(5), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)
        BlankCell = CType(oWS.Columns(6), Excel.Range).Find(Nothing, LookIn:=xlvalues, LookAt:=xlWhole)