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

Solution 1:

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

Solution 2:

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

Solution 3:

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

Solution 4:

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