使用VBA递增Excel列参考? Z到AA，AA到AB [英] Increment excel column reference using vba? Z to AA, AA to AB

问题描述

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

Required: To refer the column values in a list.

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

There are n number of rows in one sheet and each cell has a list that is referenced from column values in another sheet. I created the following code but it breaks after Z because the ASCII values are not for AA, AB,...

如何使用VBA为所有行创建列表?

How to create the list for all the rows using 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

推荐答案

使用 Range.Address属性 和 external:= true 来捕获工作表名称以及单元格范围地址. Range.Offset属性在循环中递增时会错开选择范围.

Use the Range.Address property with external:=true to capture the worksheet name as well as the cell range address. The Range.Offset property staggers your selection as you increment through the loop.

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

这篇关于使用VBA递增Excel列参考? Z到AA，AA到AB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！