问题描述
我需要一个可以输出如下内容的公式:
I need a formula that would output something like this:
如果特定文件夹中的文件名包含(因为文件名在@ 字符后会有一个额外的字符串),那么如果文件名包含单元格值 AB1,则在单元格 AC1 中添加完整的文件名.
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.
这对 VBA 可行吗?
Is that doable with VBA?
非常感谢
推荐答案
当然.这是我一直使用的 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
每当我有多个值要检查时,我也会使用这个 - 它比执行 If {check1} Or {check2} Or {check3}...
执行得更好,因为它返回为一旦找到匹配项,如果 {check1}
返回 True
,则 {check42}
不会被评估:
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
这篇关于检查文件名是否包含值的 Excel VBA 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!