在 Excel VBA 中创建一个函数来测试单元格是否包含某个字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20283613/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create a function in Excel VBA to test if a cell contains a certain character
提问by user3049027
I'm looking to create a function in Excel/VBA that will look at a range of cells and return a value of TRUE if any of the cells contains a certain character (an asterisk *). There will be some blank cells, some cells will contain text and some may contain text and an asterisk. Can anyone help? Many thanks
我希望在 Excel/VBA 中创建一个函数，该函数将查看一系列单元格，如果任何单元格包含某个字符（星号 *），则返回 TRUE 值。会有一些空白单元格，一些单元格将包含文本，有些可能包含文本和星号。任何人都可以帮忙吗？非常感谢
回答by
Copy and paste the below code into a new module
将以下代码复制并粘贴到新模块中
Function ContainsStar(r As Range) as Boolean
Dim i As Long
Dim cell As Range
Dim contains As Boolean
For Each cell In r.Cells
For i = 1 To Len(cell)
If Right(Left(cell, i), 1) = Chr(42) Then
contains = True
GoTo ExitFn
End If
Next i
Next
Exit Function
ExitFn:
ContainsStar = contains
Exit Function
End Function
then use it in the spreadsheet like this
然后像这样在电子表格中使用它
Note:
笔记：
D1 = =ConstainsStar(A1:A3)
D1 = =ConstainsStar(A1:A3)
H1 = =ConstainsStar(E1:E3)
H1 = =ConstainsStar(E1:E3)
回答by Patrick Lepelletier
you can also use the instr function
你也可以使用 instr 函数
function containstar(byval r as range) as boolean
dim cel as range 'be careful about variable names that are already existing code (cell)
for each cel in r.cells
'or use a loop like: for i=1 to r.cells.count , and use r.cells(i), if you prefer.
if instr(1,cel.value,"*")>0 then
containstar=true
exit sub 'you can also exit for
end if
next cel
containstar=false
end sub
and to call the function:
并调用函数：
a=containstar("a1:b8")
use only goto when you cannot do else, usually you don't need it.
仅当您不能执行其他操作时才使用 goto，通常您不需要它。
