vba - 使用vba增加Excel列引用？ Z 至 AA，AA 至 AB

Question

必需:引用列表中的列值。

一张工作表中有 n 行，每个单元格都有一个从另一张工作表中的列值引用的列表。我创建了以下代码，但它在 Z 之后中断，因为 ASCII 值不适用于 AA、AB、...

如何使用 VBA 创建所有行的列表？

Sub createList()
'creating custom list referencing cells from another sheet

Sheets("Checklist").Select
Dim i As Integer

For i = 1 To 100

    Dim k As String
    k = "='Parameter Options'!$" & Chr(64 + i) & "$1:$" & Chr(64 + i) & "$10"

    'Parameter Options is the sheet i am taking list values from

    Range("A" & i & ":C" & i).Select

    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=k
    End With

Next i
End Sub

Answer 1

使用Range.Address property使用 external:=true 捕获工作表名称以及单元格范围地址。 Range.Offset property当您在循环中递增时，会错开您的选择。

Sub createList()
    'don't declare your vars inside a loop!!!
    Dim k As String, i As Long

    For i = 1 To 100

        With Worksheet("Parameter Options")
            k = "=" & .Range("A1:A10").Offset(0, i - 1).Address(external:=True)
            'debug.print k
        End With

        'Parameter Options is the sheet i am taking list values from
        With Worksheets("Checklist").Range("A" & i & ":C" & i).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                Operator:=xlBetween, Formula1:=k
        End With

    Next i
End Sub