通过VBA在数据验证中使用间接函数 [英] Using Indirect function in Data Validation through VBA

问题描述

 
我正在VBA中使用我的Excel表单中的间接函数，当我尝试替换单元格地址时，我收到错误。 code> =间接（F5）
  

我无法将F5一个变量是我的代码。

  With Selection.Validation 
 .Delete 
 .Add Type：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= _ 
 xlBetween，Formula1：== indirect（F5）
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle = 
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
结束
  

解决方案

使用范围

  With Selection.Validation 
 .Delete 
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= _ 
 xlBetween，Formula1：== INDIRECT（& Range （F5）。地址（False，False）& $
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle =
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
结束
  

I am using the indirect function in my excel sheet through VBA , I am getting an error when I try to substitute the cell address in.

=indirect(F5)

I am not able to substiture the "F5" with a variable here is my code.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=indirect(F5)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

解决方案

Using a range

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=INDIRECT(""" & Range("F5").Address(False, False) & """)"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

这篇关于通过VBA在数据验证中使用间接函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！