Count occurrences of filename in column

[Lucky]

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!

 

[Mike H]

Hi,

Try this

lastrow = Cells(Cells.Rows.Count, "M").End(xlUp).Row
Set MyRange = Range("M1:M" & lastrow)
For Each c In MyRange
If UCase(Left(c, 6)) = "MYFILE" And _
UCase(Right(c, 4)) = ".XLS" Then
Count = Count + 1
End If
Next


Mike

 

[Lucky]

Thanks, Mike. Yours doesn't use Find and FindNext, but it's cleaner
than what I have, so I'll probably use it. One little problem is that
if one of the occurrences if removed for some reason, then the count
will be off. For instance, if the highest numbered entry is
"myfile(8).xls" and for some reason "myfile(4).xls" has been removed,
then just counting and adding 1 would duplicate one. Only 7 would be
counted, and "myfile(8).xls" would be added again. So, I have to use
something like

If InStr(Cells(g, "h"), "myfile") = 1 And InStr(Cells(g, "h"),
".xls") > 0 Then
myStart = InStr(Cells(g, "h"), "(")
If myStart > 0 Then myEnd = InStr(Cells(g, "h"), ")")
If myEnd > 0 Then
nbrstr = Mid(Cells(g, "h"), myStart + 1, myEnd -
1)
If Val(nbrstr) > biggest Then biggest = Val
(nbrstr)
End If
End If

Then, I have to add 1 to the biggest and use that for the next one.
Not really hard to do, but it does slow down things a bit. Only way I
know to do it,though. Any ideas on this? Thanks again!

 

[Jacob Skaria]

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

 

[Lucky]

Thanks, Jacob. Your sub works well, too. Now I have two good ones to
choose from. I presume Find and Find Next will be a little faster
than the other method. I also like your Split better than the way I
did it. By the way, I don't have a Yes button to click.

 

[Stefano]

If you want to speed it up you can go through an array instead of going
through the cells:

Dim R As Long, Values As Variant
Values = Range("H1:H" & UsedRange.Rows.Count) 'or whatever your range is
For R = 1 To UBound(Values, 1)
If InStr(Values(R, 1), "something") Then DoSomething
Next R

Stefano