数据验证和字符串构造出错-Java 学习之路

我正在尝试根据另一个工作表中的一系列单元格向一组单元格添加数据验证 . 问题是另一个工作表中的单元格范围不是静态的，可以更改 .

总的来说，我正在寻找A10中的一组下拉框：A29细胞中含有成分

当我使用= INDIRECT（“Ingredients！A2：A320）时，只使用excel验证向导它可以工作，但我需要结束单元格是动态的 .

我有这个当前的vba代码

Dim endrow As Integer

endrow = Sheets("Ingredients").Range("A" & Rows.Count).End(xlUp).Row

Range("A10:A29").Select
 With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="=INDIRECT(" & Chr(34) & "Ingredients!A2:A" & endrow & Chr(34) & ")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

我在这段代码上得到1004错误 .

为了让任何看到这个的人更容易，我在公式部分的目标是：

=INDIRECT("Ingredients!A2:A*endrow*)

3 回答

0
说明：偏移移动范围成分！$ A $ 1 by 1 down并将范围的高度调整为列中的非空单元格数量！$ A：$ A减去1列表的 Headers
```
=OFFSET(Ingredients!$A$1,1,0,COUNTA(Ingredients!$A:$A)-1,1)
```
回复于 2024-04-29T06:01:08+08:00
1
这里评论的是我对没有VBA的问题的回答：

This Demo is done using Excel 2010:

从功能区选择 Formula Tab

然后选择 Name Manager .

选择 New .
提供命名范围的名称（在我的示例中为 MyRange ） .
然后将下面的公式放在 Refers To 中 .
```
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)
```
按 OK .
请注意，我假设 Sheet1 为验证列表源 .

您将返回 Name Manager 对话：
请注意，您已新添加了新创建的名称 .
按 Close

现在，转到 Validation .
在验证对话框中，选择 Allow 字段中的 List .
然后在 Source 中输入以下公式 .
```
=MyRange
```
当然你需要按 OK .

Important: 您可以在此处直接输入命名范围中使用的公式 .
但是，具有命名范围可提供数据可见性 .

假设我们在Sheet1 Cell C1中应用 Validation ，它将如下所示：

添加其他项目或数据将导致：

请注意，验证列表会自动调整 .
希望这对你有所帮助 .

EDIT1: 如果您想坚持自己的逻辑，可以使用以下代码在VBA中完成上述操作：
```
Sub DynamicValidation()

Dim ws As Worksheet
Dim rng As Range, valrng As Range
Dim valformula As String

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("C1") '~~> where you want to put the validation
'~~> check if you have list, exit if none
If ws.Range("A2").Value = "" Then Exit Sub
'~~> Below contains your dynamic range
Set valrng = ws.Range(ws.Range("A2"), ws.Range("A" & ws.Rows.Count).End(xlUp))
'~~> your formula based on your dynamic range
valformula = "=" & valrng.Address
'~~> actual application of validation
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=valformula '~~> use the variable here
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub
```
以上代码经过试用和测试 .
每次运行宏时，它都会更新验证列表 .
只需根据您的需要调整参考 .
回复于 2024-04-29T06:01:08+08:00

你得到运行时错误1004的原因是因为chr（34） . 你不需要它 . 一旦你修复了它你将得到一个运行时错误13因为你试图在这里连接一个字符串与整数：

Range("A" & Rows.Count)

您需要将整数转换为字符串：

Dim endrow As Integer

endrow = Sheets("Ingredients").Range("A" & strings.trim(str(Rows.Count))).End(xlUp).Row

Range("A10:A29").Select
 With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="=INDIRECT("Ingredients!A2:A" & strings.trim(str(endrow)))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

你也可以看到我写的这篇文章Excel VBA, Common Errors When Using Strings

回复于 2024-04-29T06:01:08+08:00

数据验证和字符串构造出错

3 回答

相关问题