Runtime Error 13 Type Mismatch

[USCguy09]

I have a macro that calls another macro whenever i open the excel file but sometimes, I get this "Runtime Error 13 Type Mismatch" message.

What does this exactly mean?
Can someone help me what the issue is with the coding?

Sub Auto_Open()
Call auto_refresh
End Sub

Sub auto_refresh()

' execute macros
Range("D18").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D19").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D20").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D21").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D22").Select
    ActiveCell.FormulaR1C1 = "=countfilesif(R[-8]C,R[-8]C[3],FALSE,R[-8]C[5])"
    Range("D18").Select
' submit macro to run again in 5 min
Application.OnTime Now + TimeValue("00:05:00"), "auto_refresh"

End Sub

 

[Norie]

Where's the rest of the code?

For example the countfilesif function?

Also why is the code running every 5 minutes?

 

[Ruddles]

It often means you're trying to treat a non-numeric value as a number.

When you answer Norie's question, show us the line of code which is highlighted when the program stops.

 

[USCguy09]

Where's the rest of the code?

For example the countfilesif function?

Also why is the code running every 5 minutes?

oops forgot. see below. i have it running every 5 minutes b/c the countfiles function won't update unless I select the cells (double click on it) and push enter. If more files get transferred to my specified directories, the workbook won't reflect the new number unless i have it running every 5 minutes like i have it.

Dim COUNTFILES As Long

Public Function COUNTFILESIF(ByVal FolderPath As String, ByVal Extn As String, _
Optional IncludeSubFolder As Boolean, _
Optional ByVal Criteria As String) As Long

Dim FileName As String
Dim strExtn As String
Dim blnSkipCrit As Boolean

If Right$(FolderPath, 1) <> "\" Then FolderPath = FolderPath & "\"
If Not CBool(Len(Dir(FolderPath, vbNormal))) Then
COUNTFILESIF = CVErr(2042)
Exit Function
End If
COUNTFILES = 0
Extn = LCase$(Replace(Extn, ".", ""))
FileName = LCase$(Dir(FolderPath & "*." & Extn))
If Len(Criteria) Then
Criteria = LCase$(Criteria)
Else
blnSkipCrit = True
End If
Do While Len(FileName)
strExtn = LCase$(Mid$(FileName, InStrRev(FileName, ".") + 1))
If strExtn Like Extn Then
If Not blnSkipCrit Then
If InStr(1, FileName, Criteria) Then
COUNTFILESIF = COUNTFILESIF + 1
End If
Else
COUNTFILESIF = COUNTFILESIF + 1
End If
End If
FileName = LCase$(Dir())
Loop

If IncludeSubFolder Then
SubFoldersFilesCount FolderPath, Extn, Criteria
COUNTFILESIF = COUNTFILESIF + COUNTFILES
End If

End Function
Private Sub SubFoldersFilesCount(ByVal Folder, ByVal Extn As String, _
Optional ByVal Criteria As String)
Dim objFSO As Object
Dim objFolder As Object
Dim strExtn As String
Dim blnSkipCrit As Boolean

If objFSO Is Nothing Then
Set objFSO = CreateObject("Scripting.FileSystemObject")
End If
Set Folder = objFSO.GetFolder(Folder)

For Each SubFolder In Folder.SubFolders
Set objFolder = objFSO.GetFolder(SubFolder.Path)
For Each FileName In objFolder.Files
strExtn = LCase$(Mid$(FileName, InStrRev(FileName, ".") + 1))
If strExtn Like Extn Then
If Not blnSkipCrit Then
If InStr(1, LCase$(FileName.Name), Criteria) Then
COUNTFILES = COUNTFILES + 1
End If
Else
COUNTFILES = COUNTFILES + 1
End If
End If
Next
SubFoldersFilesCount SubFolder, Extn, Criteria
Next

End Sub

 

[USCguy09]

It often means you're trying to treat a non-numeric value as a number.

When you answer Norie's question, show us the line of code which is highlighted when the program stops.

hmmmm. still a bit confused b/c not sure where in my workbook this would apply. when the runtime error message pops up, i can't debug it.
it still shows the correct number of files though.

 

[Norie]

I don't think I've seen many UDFs like that.

This is a stupid question, but what is it meant to do?

Are you trying to count the no of files that match some criteria in a particular folder(s)?

If that's the case, another stupid question, why?

Also, why not just recalculate the code when you want rather than every 5 minutes?

You could set up a button that only calculates the cells with the formula in, or even just hit F9 to calculate everything.

 

[USCguy09]

I don't think I've seen many UDFs like that.

This is a stupid question, but what is it meant to do?

Are you trying to count the no of files that match some criteria in a particular folder(s)?

If that's the case, another stupid question, why?

Also, why not just recalculate the code when you want rather than every 5 minutes?

You could set up a button that only calculates the cells with the formula in, or even just hit F9 to calculate everything.

i didn't come up with the UDF, just found it online. Here's the link:
http://www.excelfox.com/forum/archive/index.php/t-64.html?
In fact, i don't know much about vba other than to tweak it a little. I plan on learning it but hadn't had much time lately.

The UDF basically does exactly what you stated. It counts the number of files in a specified directory based on multiple parameters.
I'm using this UDF for work related purposes. I have an internal work order submitting system. We have a bunch of people using this work order form i created and when they submit it, it saves it in a specified directory. I use this UDF just as a way of keeping track of how many comes in instead of looking at the folders all the time.

I have actually tested and tried hitting F9 after I put some sample files in the directory but the numbers didn't change. It only updated when I double clicked on the cell containing the function and pushing enter.
I also didn't want to click on a button every 5 minutes. I wanted to make this as easy and hassle free as possible. Hope this clears up some of your questions.

 

[Norie]

So would something that displayed the no of files when you clicked a button be something you could use?

If you can give more details about how you are counting the files, eg parameters, folders etc, then we might be able to help with something.

 

[Jonmo1]

oops forgot. see below. i have it running every 5 minutes b/c the countfiles function won't update unless I select the cells (double click on it) and push enter. If more files get transferred to my specified directories, the workbook won't reflect the new number unless i have it running every 5 minutes like i have it.

For this problem, try adding this as the first line inside the countfilesif function

Application.Volatile