根据现有的VBA函数将数据验证应用于许多单元格

Question

我不知道如何使用公式在循环中运行字符串。

这是我尝试转换为公式的 vba 代码。我想使用此公式对各种单元格进行数据验证。

公式：

Function Test(pValue) As Boolean
    If Len(pValue) < 2 Or Len(pValue) > 99 Then
        AlphaNumeric = False
        Exit Function
    End If
    LPos = 1
    LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.0123456789"
    While LPos <= Len(pValue)
        LChar = Mid(pValue, LPos, 1)
        If InStr(LValid_Values, LChar) = 0 Then
            Test = False
            Exit Function
        End If
        LPos = LPos + 1
    Wend
    Test = True
End Function

我写的公式的简单部分：

=IF(AND(LEN(E4)>1,LEN(E4)<100,____Formula_Here____),TRUE,FALSE)

我假设如果我选择E4:E50000并单击 DataValidation ，从下拉列表中选择 Custom 然后输入上述公式，然后它将自动将其应用于 E4、E5、E6。E50000也是。如果我弄错了，请纠正我。

另外，我希望它在 Excel 2003 及更高版本上运行。

Answer 1

填

空

NOT(ISERROR(SUM(SEARCH(MID(E4,ROW($1:$99),1)," ABCDEFGHIJKLMNOPQRSTUVWXYZ.0123456789"))))

这是一个数组公式，因此如果在工作表中使用，请使用 ctrl-shift-enter 输入它。但是，数据验证的自定义公式（至少在 2003 年）会自动将其视为数组公式，因此只需正常输入即可。

让我们从内到外分解它。 数组公式内的ROW($1:$99)提供了循环机制。这将导致周围的数组公式在i = 1, 2, ..., 99中的每一个求值，所以MID将依次取E4的第i个字符。 SEARCH不区分大小写，因此我们可以省略a..z，因为我们有A..Z.它返回找到第 i 个字符的A..Z.0..9位置，否则返回错误。 SUM聚合这些位置，并将传播任何错误。因此，如果未找到任何字符，则整个SUM都将是错误。

请注意，据我

所知，您可以在自定义验证公式中使用 UDF，所以我不确定我是否遵循您的反对意见。

Answer 2

可以使用公式中的函数：http://office.microsoft.com/en-us/excel-help/creating-custom-functions-HA001111701.aspx

Answer 3

我的想法是使用一堆嵌套的替代函数将有效条目减少到 " 并进行数据验证以确保它是 "。

下面是嵌套替换。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," ",""),"a",""),"b",""),"c",""),"d",""),"e",""),"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m",""),"n",""),"o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u",""),"v",""),"w",""),"x",""),"y",""),"z",""),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),".",""),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

事实证明，这对你没有多大好处，因为我无法将其复制粘贴到数据验证中，但我用 VBA 写了这个，一切都没有丢失。

Sub jf()
Dim lValid_values As String
Dim rowcount As Long
lValid_values = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.0123456789"
Dim form As String
Dim desired_column As String
Dim desired_rowMin As Long
Dim desired_rowMax As Long
Dim InitialCell As String
Dim temp As String
desired_column = "E"
desired_rowMin = 4
desired_rowMax = 50000
InitialCell = desired_column & desired_rowMin
form = "substitute(||cell||," & """" & " " & """" & "," & """" & """" & ")"
Do While lValid_values <> ""
form = "substitute(" & form & "," & """" & Left(lValid_values, 1) & """" & "," & """" & """" & ")"
lValid_values = Right(lValid_values, Len(lValid_values) - 1)
Loop
form = "=AND(LEN(||cell||)>1,LEN(||cell||)<100," & form & "=" & """" & """" & ")"
For rowcount = desired_rowMin To desired_rowMax
temp = Replace(form, "||cell||", desired_column & rowcount)
With Range(desired_column & rowcount).Validation
    .Delete
    .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=temp
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With
Next rowcount
End Sub

我开始运行这个到 50000，但它很慢，所以我把它停在 4000 左右。 它确实有效，所以希望它不是你必须多次做的事情。 您必须更改desired_column、desired_rowmin和desired_rowmax变量以满足您的需求。

Answer 4

我发现这样做的方法是使用帮助程序列。

假设您的数据处于E4:E50000

F4 将是=Test(E4)
将此值向下复制到末尾，因此您现在拥有了帮助器列（如果要检查，最后一个将是=Test(E50000) ）

然后=F4数据验证自定义公式（当您在选择范围 E4：E50000 的情况下输入此公式时，它将将其向下复制，并调整引用以指向旁边的相应单元格）。

您的代码中也有一个错误 - 您有 AlphaNumeric = False ，当它应该Test = False时。设置显式选项将有助于查找这些错误。

---

我在测试电子表格中使用的例程的略微修改版本：

Option Explicit
Function Test(pValue) As Boolean
Dim lPos As Long
Dim lValid_Values As String
Dim LChar As String
If Len(pValue) < 2 Or Len(pValue) > 99 Then
    Test = False
    Exit Function
End If
lPos = 1
lValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ.0123456789"
While lPos <= Len(pValue)
    LChar = Mid(pValue, lPos, 1)
    If InStr(lValid_Values, LChar) = 0 Then
        Test = False
        Exit Function
    End If
    lPos = lPos + 1
Wend
Test = True
End Function