Increment excel column reference using vba? Z to AA, AA to AB

Question

Required: To refer the column values in a list.

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,...

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

Try using a column number with a function to convert it to a letter -more info here: [Function to convert column number to letter?](http://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter) — Dawid SA Tokyo, May 27 '16 at 06:41

score 6 · Accepted Answer · answered May 27 '16 at 07:07

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

score 5 · Answer 2 · edited May 23 '17 at 12:08

5

You don't need column letters when working with code.

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

edited May 23 '17 at 12:08

Community

1
1

answered May 27 '16 at 07:19

GSerg

76,472
17
159
346

Haven't seen the [Application.ConvertFormula Method](https://msdn.microsoft.com/en-us/library/office/ff822751.aspx) for a while. Good use of it here. – May 27 '16 at 07:44

ib11 · Answer 3 · 2016-05-27T08:28:45.763

4

Using your code, if you add this modification it will convert double-lettered columns properly, but as noted as in the comment, you are better off using column numbers, more straightforward.

However for a simple quick solution, this will do it:

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

edited May 27 '16 at 08:28

answered May 27 '16 at 07:02

ib11

2,530
3
22
55

1

Should there be a third possibility for triple letter columns? – May 27 '16 at 07:08
@Jeeped - I tested it in Excel2003 and 2 letters are ok, but if that is not the case in higher versions, sure I can add. But per the OP it is 1 to 100 that is up to DD, not more. – ib11 May 27 '16 at 08:27
This code works for me, but the issue is with AZ, it is taking as B@, may I know what could the reason. – Anita Mehta Jul 04 '22 at 15:09

score 1 · Answer 4 · answered Oct 20 '17 at 16:27

This is my solution. I generate a cycle inside another, to handle the values that happen to the Z in code ASCII.

Hope this can help you:

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

score 0 · Answer 5 · answered Oct 21 '17 at 17:53

A solution to find the columnletters also for columns greater than 702 (= three letters) using modulo arithmetics. Note: It doesn't check validity.

Code

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

Increment excel column reference using vba? Z to AA, AA to AB

5 Answers5

Linked