VB代码中如何用Formula1定义下拉框内容:

Question

Range("B5").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(WAREHOUSE!$A1$A100)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With

运行到这一步报错： xlBetween, Formula1:="=INDIRECT(WAREHOUSE!$A1$A100)"，是否写法有问题
在VB代码中xlBetween, Formula1:="WAREHOUSE!$A1$A100"写法是没有效果的，下拉框内容变成了WAREHOUSE!$A1$A1，但是按照第一个写法会报错。

Answer 1

要是同一张表简单直接Formula1:="=a1:a100"

跨表也用过老是不行，我是用笨办法
Formula1:=Join(Application.Transpose(Sheets(“WAREHOUSE”).Range("A1:A100")), ",")本回答被提问者采纳