验证公式的结果必须在代码运行时返回一个结果。如果HLOOKUP引用的单元格为空，则Excel将看到Source错误，代码将终止。如果手动输入引用时出现此错误，Excel允许您继续操作，但您不能(据我所知(在VBA中执行此操作。要解决此问题，请在输入验证详细信息之前，在引用的单元格中输入一个(合法(值，然后删除引用值。例如：
我想在VBA中的数据验证中应用Excel的INDIRECT函数。
我正在使用VBA填充数据验证条件，并使用INDIRECT通过HLOOKUP从另一个命名范围中查找适当的工作簿命名范围。
我认为我的问题在于VBA代码的语法。我不知道双引号的正确位置是什么。这将返回一个错误400通知。
我有以下内容(为了清晰起见，省略了功能代码部分(。HLOOKUP引用的列F包含一个用户可选择的值，DepartmentRef是一个命名范围：
Sub AddNewRoom()
Dim targetRow As Integer
targetRow = Range("EndRoomData").Row
With Worksheets("RoomData")
With .Range("G" & targetRow).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect(hlookup(F" & targetRow & ",DepartmentRef,2,false))"
End With
End With
End Sub
当我更改验证公式以删除代码变量时，代码功能正常：
With .Range("G" & targetRow).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect(hlookup(F7,DepartmentRef,2,false))"
End With
不要使用直接的"name"单元格引用，而是尝试：
Formula1:="=INDIRECT(HLOOKUP(" & Worksheets("RoomData").Cells(targetRow, 6).Address(False, False) & ",DepartmentRef,2,FALSE))"
With Worksheets("RoomData")
.Range("F" & targetRow).Value = ThisWorkbook.Names("DepartmentName").RefersToRange(1, 1)
strFormula = "=INDIRECT(HLOOKUP(" &
Worksheets("RoomData").Cells(targetRow,6).Address(True, True) & ",DepartmentRef,2,FALSE))"
With .Range("G" & targetRow).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strFormula
End With
.Range("F" & targetRow).ClearContents
End With