Run-Time error "13" on a data query

[Kickercoach1285]

Hi guys,
I have been reading this forum but never joined until today. I have an issue. We have an Excel spreadsheet that has multiple macros running in it and was created by another controls engineer at out plant. He has since moved on and we are now trying to run his Data Query that is in the spreadsheet but we get the Run-time error "13" Type Mismatch. I know Excel somewhat and just some easier VB stuff. Nothing like what I am seeing you guys post. Any help would be helpful.

Debug is getting hung up on the red text below.

'___SUBMIT button pressed - RUN QUERY___'
Private Sub submit_Click() 'RUN DATA QUERY
Dim submitLoop1 As Integer, sortTest As Integer, sortTest2 As Integer, copyLoop1 As Integer, pasteRow As Integer
Dim startDate As Date, stopDate As Date
'__Turn the WORKING label to visible___'
dataQuery.dataQ16.Visible = True
Me.Repaint

'___Create the start and stop dates to use in the comparisons, and add to the OT Report___'
startDate = dataQ1 & "/" & dataQ2 & "/" & dataQ3
stopDate = dataQ4 & "/" & dataQ5 & "/" & dataQ6
Worksheets("dataHIDE").Range("A45").Value = startDate
Worksheets("dataHIDE").Range("A46").Value = stopDate
'___Clear the data on the Query_Data tab___'
'___delete rows___'
Sheets("Query_Data").Rows("4:" & logLastRow).Delete

'___Copy the data from the log to Query_Data___'
For copyLoop1 = 4 To logLastRow
If (CDate(Sheets("Log").Cells(copyLoop1, "A").Value) >= CDate(Worksheets("dataHIDE").Range("A45").Value)) And (CDate(Sheets("Log").Cells(copyLoop1, "A").Value) <= CDate(Worksheets("dataHIDE").Range("A46").Value)) Then
'___Copy the row to the Metrics Data Sheet___'
pasteRow = (Sheets("Query_Data").Cells(Rows.Count, "A").End(xlUp).Row) + 1
Sheets("Log").Rows(copyLoop1).Copy Sheets("Query_Data").Rows(pasteRow)
End If
Next copyLoop1

'___Parse out data not requested___'
'___Shift___'
If Not dataQuery.Controls("dataQ7") = Nullstring Then
Call matchCriteria("B", 7, 0)
End If

'___Station___'
If Not dataQuery.Controls("dataQ8") = Nullstring Then
Call matchCriteria("C", 8, 0)
End If

'___Robot/Pallet___'
If Not dataQuery.Controls("dataQ9") = Nullstring Then
Call matchCriteria("D", 9, 0)
End If

'___Tool___'
If Not dataQuery.Controls("dataQ10") = Nullstring Then
Call matchCriteria("E", 10, 0)
End If

'___Category___'
If Not dataQuery.Controls("dataQ11") = Nullstring Then
Call matchCriteria("G", 11, 0)
End If

'___Issue___'
If Not dataQuery.Controls("dataQ12") = Nullstring Then
Call matchCriteria("H", 12, 0)
End If

'___Downtime Occurence Time (Min) Keep data row if downtime is less than or equal to the Form Value___'
If Not dataQuery.Controls("dataQ13") = Nullstring Then
Call matchCriteria("I", 13, 1)
End If

'___Downtime Occurence Time (Min) Keep data row if downtime is greater than or equal to the Form Value___'
If Not dataQuery.Controls("dataQ14") = Nullstring Then
Call matchCriteria("I", 14, 2)
End If

'___Manufacturer___'
If Not dataQuery.Controls("dataQ15") = Nullstring Then
Call matchCriteria("F", 15, 0)
End If

'___Set to Query_Data sheet___'
Sheets("Query_Data").Select

'__Turn the WORKING label to INvisible___'
dataQuery.dataQ16.Visible = False
Me.Repaint

End Sub

 

[steve the fish]

Hi. Id imagine you have text or an error in one of your cells. Find the value of copyLoop1 when the macro fails. Then look in the appropriate cells for text/ error cells.

 

[Kickercoach1285]

Thanks Steve for replying I'll look.

 

[Kickercoach1285]

From what I can understand it seems to be getting hung up on the date column?

So I set the query to a very small manageable date range of 2 days and verified the column is a date format and it is only dates and no text in that "A" column and it still gets hung up.

There is another Metrics query I also tried and it gets hung up on the line containing the date also.

 

[steve the fish]

Presumably those dates arent true dates. Try using =ISNUMBER(A4) on them. If they are true dates it will return TRUE.

 

[Kickercoach1285]

I started looking into what Steve said that it may have something to do with a text entry... I haven't found it 100% yet but I did make a copy of the spreadsheet and basically made all the dates in column "A" 2 dates 8/11/2016 and 8/10/2016 for all 1800 rows and the thing didn't error out so we're on the right track.

 

[Kickercoach1285]

Steve the Fish you should change your name to "Steve the Man". I ran the =ISNUMBER for the entire A column and sure enough the controls engineer on evening shift had keyed in text in that column in several places. I would have never known or even thought about using the formula =ISNUMBER to troubleshoot the spreadsheet.

I thank you so much.

We have an Ops Review coming up next week and I was planning on using this spreadsheet to print out charts about our downtime "Big Hitters" and I couldn't get it to work. You just saved me a ton of work.

Again I thank you.