如何使用 VBA 增加 Excel 的列引用?从 Z 到 AA,从 AA 到 AB。

9 浏览
0 Comments

如何使用 VBA 增加 Excel 的列引用?从 Z 到 AA,从 AA 到 AB。

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

一个表中有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

admin 更改状态以发布 2023年5月21日
0
0 Comments

在使用代码时,不需要列字母。

Sub createList()
  'creating custom list referencing cells from another sheet
  Dim i As Long
  For i = 1 To 100
    Dim k As String
    k = "='Parameter Options'!R1C" & i & ":R10C" & i
    With Worksheets("Checklist").Range("A" & i & ":C" & i).Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Application.ConvertFormula(k, xlR1C1, xlA1)
    End With
  Next i
End Sub

0
0 Comments

使用Range.Address属性并将external参数设置为true,以获取工作表名称和单元格范围地址。使用Range.Offset属性,当您逐步增加循环时,会按阶段选择。

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

0