Try the below...Columns(13) refer to Column M...

Sub Macro2()
Dim varFound As Variant, strAddress As String
Dim intFileNum As Integer, intFileCount As Integer, intLargeFileNum As Integer

With Worksheets("Sheet1").Columns(13)
Set varFound = .Find("myfile", , xlValues, xlPart)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
If InStr(1, varFound, ".xl", vbTextCompare) 0 Then
intFileCount = intFileCount + 1
If InStr(varFound, "(") Then
intFileNum = Split(Mid(varFound, InStr(varFound, "(") + 1), ")")(0)
End If
If intFileNum intLargeFileNum Then intLargeFileNum = intFileNum
End If
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address < strAddress
End If
End With

MsgBox "FileCount: " & intFileCount & vbLf & _
"LatestFile: " & intLargeFileNum
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Lucky" wrote:

I want to use VBA to count the occurrences of a filename in column M,
such as "myfile.xls". Problem is, each time the name appears (after
the first one), it'll include a number, like "myfile(2).xls", "myfile
(3).xls", and so on. Also, I can't just count occurrences of the name
because it could appear as a non-filename ("myfile"), without the .xls
extension, and I don't want to count that. When I have finished
counting them, I want to add another instance, with a higher number
than the existing ones, also in column M. I've been experimenting
with Find and FindNext (because that's faster than looking for
"myfile" and ".xls" in each cell of column M. Any help would be
appreciated!
.