Geek Answers Handbook

Excel column link increment using vba? Z - AA, AA - AB

Required: to reference column values ​​in a list.

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

How to create a list for all 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
+4
source share
5 answers

Use the Range.Address property with external: = true to capture the name of the worksheet, as well as the address of the range of cells. The Range.Offset property breaks your selection as you increase 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
+5
source

.

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
+5

, , , , , , .

:

Dim i As Integer

    Dim k As String
    Dim col As String

For i = 1 To 100

    If i < 27 Then
        col = Chr(64 + i)
    Else
        col = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
    End If

    k = "='Parameter Options'!$" & col & "$1:$" & col & "$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
+3

. , , Z ASCII.

, :

For i = 0 To RecordSet.Fields.Count - 1 'This is my data source
    If Ascii > 90 Then
        Ascii = 65
        For y = i To RecordSet.Fields.Count - 1
            Hoja1.Range("A" & Chr(Ascii) & 3).Value = RecordSet.Fields(y).Name
            Ascii = Ascii + 1
        Next
    Else
        Hoja1.Range(Chr(Ascii) & 3).Value = RecordSet.Fields(i).Name
        Ascii = Ascii + 1
    End If
Next
+1

702 (= ) . . .

Function colChar(colNo As Long) As String
If colNo < 1 Then Exit Function
Dim n As Long
Dim c As Byte
Dim s As String

n = colNo
Do
    c = ((n - 1) Mod 26)    ' locate within A-Z
    s = Chr(c + 65) & s     ' combine characters
    n = (n - c) \ 26        ' check the rest
Loop While n > 0
colChar = s                 ' return character(s)
End Function
0

More articles:


All Articles