excel - 无法正确获取自定义函数语法:包含()
问题描述
我正在拼命尝试将此解决方案应用于我自己的代码:https ://stackoverflow.com/a/43196497/11632049
目标是搜索各种字符串(“ABC”、“123”、...),如果数据行包含该字符串,则将该行复制到不同的工作簿。我收到子或函数未定义错误
Public Function ContainsAny(ByVal needle As String, ByVal caseSensitive As _
Boolean, ParamArray haystack() As Variant) As Boolean
Dim k As Integer
Dim found As Boolean
For k = LBound(haystack) To UBound(haystack)
found = Contains(needle, CStr(haystack(k)), caseSensitive)
If found Then Exit For
Next
ContainsAny = found
End Function
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim CoderBook As Workbook
Dim Review As Workbook
Dim Alpha As Worksheet
Dim Coder As Worksheet
Dim LastRow As Long
Dim NextRow As Long
Dim i As Long
Set CoderBook = Workbooks.Open(FilePath)
Set Coder = CoderBook.Sheets("Sheet1")
Set Review = ThisWorkbook
Set Alpha = Review.Sheets("Sheet5")
'Search code
LastRow = Alpha.Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Application.ScreenUpdating = False
'Loop search code
For i = 2 To LastRow
If Alpha.Range("G" & i) <> Alpha.Range("H" & i) Or _
Alpha.Range("J" & i) <> Alpha.Range("K" & i) Then
Select Case True
Case ContainsAny(Ophth.Range("H" & i), False, "ABC", "123")
DuplicateCheck = Application.Match(Alpha.Range("A" & i).Value, _
Coder.Columns(1), 0)
If IsError(DuplicateCheck) Then
Coder.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0). _
EntireRow.Value = Alpha.Rows(i).Value
End If
End Select
End If
Next i
CoderBook.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub
解决方案
您收到“未定义函数”编译错误,因为ContainsAny
正在调用Contains
您删除的函数。加回来。
found = Contains(needle, CStr(haystack(k)), caseSensitive)
Contains
但是,需要翻转该方法的逻辑,以使其按照您想要的方式工作:
Public Function Contains(ByVal needle As String, ByVal haystack 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, haystack, needle, compareMethod) <> 0)
If Not Contains Then Contains = (InStr(1, needle, haystak, compareMethod) <> 0)
End Function
当任一字符串包含另一个字符串时,这种方式Contains
将返回True
- 并且您可以这样做:
If ContainsAny(Ophth.Range("H" & i).Value, False, "ABC", "123", "XYZ") Then
当单元格值包含任何一个指定字符串时,条件块将执行。
推荐阅读
- node.js - Web3.js getBlock() 将所有块都作为待处理
- java - 集群之间的 Java 共享文件夹同步
- django - 使用 if else if 分支查看语法错误
- mysql - 每次变量出现在另一个表中时更新计数
- c++ - 可以直接初始化调用copy-constructor
- javascript - 如果搜索键与句子的任何第一个字母匹配,如何使用搜索键获取搜索结果
- c++ - CMDIFrameWndEx::UpdateWindow() 崩溃
- struct - 在Pyspark中将结构类型大量列分解为每列的两列具有键和值
- c# - Chromedriver 仍然只支持更新驱动的 79 版
- xcode - xcode中的c ++ 20模块?xcode 12 也许?