1

this is my first time asking a question here.

I have run into an issue at work where I need to copy certain rows from one workbook to another. The copying part I believe I may have solved but my issue is with something extremely simple.

To determine which rows to copy I have to look at 4 dates within the first workbook, the one check with them I am having trouble with is checking if the cell is empty of not. As every time I run my code a mismatch error 13 occurs at the check.

    Dim XX As Excel.Application    ----secondary workbook
    Dim ws1 As Object
    Dim ws As Object

    Set XX = New Excel.Application
    XX.DisplayAlerts = False

    Set ws1 = XX.Workbooks.Add
    ws1.Activate
    Set ws = ws1.ActiveSheet

    Set xl = New Excel.Application   ----primary workbook
    xl.DisplayAlerts = False

    Set wb = xl.Workbooks.Open(FilePath)

For i = 2 To wb.Sheets("SHEET1").Range("Q65536").End(xlUp).Row  --- i have the code looping through all rows of the primary workbook

        Date1 = wb.Sheets("SHEET1").Cells(i, 12).Text
        Date2= wb.Sheets("SHEET1").Cells(i, 14).Text
        Date3= wb.Sheets("SHEET1").Cells(i, 13)
        Date4 = wb.Sheets("SHEET1").Cells(i, 15)    -----  everything up to here has no errors

        If ((CDate(Date1) < Date) & (IsEmpty(Date3))) Then ---- error is always here, at Date3

            wb.Rows(i).Copy ws.Rows(ws.Cells(ws.Rows.Count, 2).End(xlUp).Row + 1)

        end if

wb is the primary workbook I am taking data from (hopefully) and ws is the workbook I am copying into.

I know the error is specifically at the empty check in the if statement because I have taken it out and left only the less than check and the code as progressed as intended. I just cannot figure out how to fix it.

I have declared the date variables for everything from string, integer, date, and variant. And have tried importing the cell values as a range and cells, with .text or .value or nothing. Then within the if statement I have tried IsEmpty, Isnull, = "", = 0, = null, added in CDate. I have literally tried everything thing in every possible combination I can think of.

Erik A
  • 31,639
  • 12
  • 42
  • 67
David Castellano
  • 17
  • 7
  • Here is a link to a similar question: https://stackoverflow.com/questions/33421574/how-to-check-if-a-date-cell-in-excel-is-empty – Brian M Stafford Jun 06 '17 at 14:42
  • thank you very much, before posting this question i had been searching for a solution since yesterday. I feel embarrassed that you managed to find one so quickly, once again thank you. – David Castellano Jun 06 '17 at 14:52

1 Answers1

0

You could try:

 Date3 = trim(wb.Sheets("SHEET1").Cells(i, 13))
 ....IsNull(Date3)

The idea here is to trim whitespace first then check for null values.

Hope this helps :)

Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • thank you for taking the time to answer, Brian pointed me to a similar question I had not seen yet. But that is a cool trick I will keep in mind for the future :) – David Castellano Jun 06 '17 at 14:54