使用以下布爾函式檢查單元格值是否等于“M8D”或“M8P”或“M20”）
Function m8_field(ByVal plf As String) As Boolean
m8_field = (plf = "M8D" Or plf = "M8P" Or plf = "M20")
End Function
我像下面這樣使用它并且它有效：
Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")
Dim arr: arr = arg.Value2
Dim r As Long
For r = 1 To UBound(arr)
If m8_field(arr(r, 2)) Then arr(r, 1) = "Good"
Next
我需要更改最后一行：
If m8_field(arr(r, 2)) Then arr(r, 1) = "Good"
進入
If arr(r, 2) = m8_field Then arr(r, 1) = "Good"
但我得到了
編譯錯誤：此部分的引數不是可選的 (
m8_field)
提前任何學習幫助將不勝感激
uj5u.com熱心網友回復：
標記串列中的字串何時匹配
- 如果您使用一個函式，它會每次每行讀取字串陣列，從而使其效率低下。
Sub m8_field_Sub()
Const GoodStringsList As String = "M8D,M8P,M20"
Dim GoodStrings() As String: GoodStrings = Split(GoodStringsList, ",")
Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")
Dim arr As Variant: arr = arg.Value2
Dim r As Long
For r = 1 To UBound(arr, 1)
If IsNumeric(Application.Match(CStr(arr(r, 2)), GoodStrings, 0)) Then
arr(r, 1) = "Good"
'Else
' arr(r, 1) = "Bad" ' or e.g. arr(r, 1) = ""
End If
Next
' Write back to the range.
'arg.Value = arr
End Sub
- 如果你想“看中”它：
Function GetGoodStrings() As String()
Const GoodStringsList As String = "M8D,M8P,M20"
GetGoodStrings = Split(GoodStringsList, ",")
End Function
Function m8_field(ByVal plf As String, GoodStrings() As String) As Boolean
m8_field = IsNumeric(Application.Match(plf, GoodStrings, 0))
End Function
Sub TestTheFunctions()
' Read only once.
Dim GoodStrings() As String: GoodStrings = GetGoodStrings
Dim arg As Range: Set arg = ActiveSheet.Range("D1:E20")
Dim arr As Variant: arr = arg.Value2
Dim r As Long
Dim plf As String
For r = 1 To UBound(arr, 1)
plf = CStr(arr(r, 2))
If m8_field(plf, GoodStrings) Then ' needs parameters
arr(r, 1) = "Good"
'Else
' arr(r, 1) = "Bad" ' or e.g. arr(r, 1) = ""
End If
Next
' Write back to the range.
'arg.Value = arr
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/485917.html