Slow Function help please

[insanity82007]

I built this function to find the last character in a string primarily for file renaming so that it would find the last "\" in a directory tree so that I could extract the file name from the list using a mid function after that.

When I run it for lots of rows it slows down heaps and takes ages to calc. Any ideas on how I can improve this?

Cheers

Function FindLast(ByVal inpt As String, what As String) As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

For i = 1 To Len(inpt)
If Mid(inpt, i, Len(what)) = what Then
FindLast = i
End If
Next i


Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic


End Function

 

[insanity82007]

Function FindLast(ByVal inpt As String, what As String) As Long
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    For i = 1 To Len(inpt)
        If Mid(inpt, i, Len(what)) = what Then
            FindLast = i
        End If
    Next i
   
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Function

 

[shg]

Maybe this:

Function FindLast(ByVal sInp As String, sWhat As String) As Long
    FindLast = InStrRev(sInp, sWhat, vbTextCompare)
End Function

 

[Boswell]

I don't think screen update and autocalcultae will speed up the execution of your function because it is not placing values in cells or causing any processes to calculate.. I could be mistaken though. This way may be faster...

Function getFileName(filePath As String) As String
    Dim pathSplit As Variant
 
    pathSplit = Split(filePath, "\")
    getFileName = pathSplit(UBound(pathSplit))
 
End Function

Untested, but should work.

*Edit - Just saw SHG's suggestion above. Was unaware of InStrRev, definitely the method I would use.