Excel VBA添加数据验证列表时Formula1触发错误13的解决求助

问题场景 #

在包含多工作表的复杂工作簿中，为"Riassuntivo"工作表的G4:K4单元格添加动态数据验证列表：公式依赖"filtri"工作表数据，且关联G5的选择结果。手动通过菜单添加公式可正常生效，但VBA调用Validation.Add时触发错误13（类型不匹配）。

原代码片段 #

Dim str2 As String, v1 As String, v2 As String, v3 As String, v4 As String
Set rngSel = Range("A1:AA1")
With rngSel
    Set rng = .Find(what:="mercato", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    iCol = rng.Column
End With
Set rngSel = Range("A1:A120")
With rngSel
    Set rng = .Find(what:="-", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
    iK = rng.Row
End With
Range("C1").End(xlToRight).Offset(0, 1).Select
iFine = ActiveCell.Column
v1 = ConvertToLetter(CLng(iCol))
v2 = ConvertToLetter(CLng(iCol + 1))
v3 = ConvertToLetter(CLng(iFine))
v4 = ConvertToLetter(CLng(iFine + 1))
' [...]
Sheets("Riassuntivo").Select
Range("G4:K4").Select
str = "=if($G$5="""""";$AY$1;OFFSET(filtri!$" & v1 & "$1;2;MATCH($G$5;filtri!$" & v2 & "$1:$" & v4 & _
"$1;0);COUNTIF(OFFSET(filtri!$" & v1 & "$1;2;MATCH($G$5;filtri!$" & v2 & "$1:$" & v4 & "$1;0);20);""<>-"")))"
With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= str
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
End With

Debug输出的公式字符串 #

=IF($G$5="";$AY$1;OFFSET(filtri!$E$1;2;MATCH($G$5;filtri!$F$1:$I$1;0);COUNT.IF(OFFSET(filtri!$E$1;2;MATCH($G$5;filtri!$F$1:$I$1;0);20);"<>-")))

问题分析与修复方案 #

错误原因 #

1. 公式分隔符不匹配：VBA中默认使用英文逗号,作为函数参数分隔符，但代码中用了分号;。手动添加时Excel会自动适配区域设置，VBA执行时则需要统一使用英文格式。
2. 函数名拼写错误：Debug输出中出现COUNT.IF（带点），正确函数名为COUNTIF（无点）。
3. 多余参数导致类型不匹配：Validation.Add中指定了Operator:=xlBetween，但数据验证列表不需要该参数，引发参数类型冲突。
4. 依赖Select操作：使用Select增加代码不稳定风险，应直接引用单元格对象。

修复后的代码 #

Dim str2 As String, v1 As String, v2 As String, v3 As String, v4 As String
Dim rngSel As Range, rng As Range
Dim iCol As Integer, iFine As Integer
Dim wsRiassuntivo As Worksheet

Set wsRiassuntivo = ThisWorkbook.Sheets("Riassuntivo")

' 获取"mercato"所在列
Set rngSel = wsRiassuntivo.Range("A1:AA1")
Set rng = rngSel.Find(what:="mercato", LookIn:=xlValues, LookAt:=xlPart, SearchDirection:=xlNext, MatchCase:=False)
If Not rng Is Nothing Then iCol = rng.Column

' 获取最后列
iFine = wsRiassuntivo.Range("C1").End(xlToRight).Offset(0, 1).Column

v1 = ConvertToLetter(CLng(iCol))
v2 = ConvertToLetter(CLng(iCol + 1))
v4 = ConvertToLetter(CLng(iFine + 1))

' 构建修正后的公式（使用英文逗号分隔参数）
str = "=IF($G$5="""",$AY$1,OFFSET(filtri!$" & v1 & "$1,2,MATCH($G$5,filtri!$" & v2 & "$1:$" & v4 & _
"$1,0),COUNTIF(OFFSET(filtri!$" & v1 & "$1,2,MATCH($G$5,filtri!$" & v2 & "$1:$" & v4 & "$1,0),20),""<>-"")))"

' 直接操作目标单元格，避免Select
With wsRiassuntivo.Range("G4:K4").Validation
    .Delete
    ' 移除多余的Operator参数
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=str
    .IgnoreBlank = True
    .InCellDropdown = True
    .ShowInput = True
    .ShowError = True
End With

额外注意事项 #

• 确保ConvertToLetter函数能正确将列号转换为字母（如5→E）。
• 若系统区域设置强制使用分号作为分隔符，可通过Application.International(xlListSeparator)获取当前分隔符替换代码中的逗号。

内容的提问来源于stack exchange，提问作者BennyB