Excel VBA function that checks if filename CONTAIN

问题:

I need a formula that would output something like this:

If a filename in a specific folder contains (because the filename will have an additional string after a @ character), so if a filename contains the cell value AB1 then add full filename in cell AC1.

Is that doable with VBA?

Many thanks

回答1:

Is that doable in VBA?

Sure. Here's a VBA helper function I use all the time:

Public Function Contains(ByVal string_source As String, ByVal find_text As String, Optional ByVal caseSensitive As Boolean = False) As Boolean

    Dim compareMethod As VbCompareMethod

    If caseSensitive Then
        compareMethod = vbBinaryCompare
    Else
        compareMethod = vbTextCompare
    End If

    Contains = (InStr(1, string_source, find_text, compareMethod) <> 0)

End Function

I also use this one whenever I have more than one value to check for - it performs better than doing If {check1} Or {check2} Or {check3}... because it returns as soon as it finds a match, so {check42} doesn't get evaluated if {check1} returned True:

Public Function ContainsAny(ByVal string_source As String, ByVal caseSensitive As Boolean, ParamArray find_strings() As Variant) As Boolean

    Dim find As String, i As Integer, found As Boolean

    For i = LBound(find_strings) To UBound(find_strings)

        find = CStr(find_strings(i))
        found = Contains(string_source, find, caseSensitive)

        If found Then Exit For
    Next

    ContainsAny = found

End Function

回答2:

Consider:

Sub qwerty()
    Dim FileSpec As String, FileName As String
    Dim v As String
    FileSpec = "C:\TestFolder\2013\Fed taxes\qwerty31416.xlsm"
    ary = Split(FileSpec, "\")
    FileName = ary(UBound(ary))
    v = Range("AB1").Value
    If InStr(1, FileName, v) > 0 Then
         Range("AC1").Value = FileName
    End If
End Sub