How to use column letters in VBA

Question

I'm currently struggling with the following problem

I'm trying to implement an input box where a user can input the character of the column.

After that, i dont know how to convert this into a number to use it in the Worksheet.Cells Method

For example: The User inputs "B", so the program saves it in a variable named x and converts it into a number so it can be used as Worksheet.Cells(Row, X)

Is there any method or does someone has an idea how to do this?

You can say column("b:b").column or use"b" in range or cells sa range("b" & row) — Nathan_Sav, Feb 10 '16 at 07:43
Possible duplicate of [Excel column number from column name](http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name) — eirikdaude, Feb 10 '16 at 07:53
`Worksheet.Cells(Row, X)` will work whether `X` is a letter or number. — Rory, Feb 10 '16 at 07:56

score 7 · Answer 1 · answered Feb 10 '16 at 08:09

7

Cells() is your friend.

Don't overthink this.

Cells(1, 1) = "jello" ' changes cell A1
Cells(2, "AZ") = "too much jello" ' changes cell AZ2

The second argument of Cells() can be either a number or the alpha column header.

answered Feb 10 '16 at 08:09

teylyn

34,374
4
53
73

Paul Ogilvie · Answer 2 · 2016-02-10T07:57:42.320

0

B is the second column, so you can use the expression (based on ASCII):

Sub main()
    Dim s As String
    s = "AB"
    example s
End Sub

Sub example(s As String)

    Dim colNum As Integer
    Dim i As Integer

    i = 1: colNum = 0
    While (Mid(s, i, 1) <> "")
        colNum = colNum * 26 + (Asc(UCase(Mid(s, i, 1))) - Asc("A") + 1)
        i = i + 1
    Wend
    MsgBox colNum
End Sub

edited Feb 10 '16 at 07:57

answered Feb 10 '16 at 07:44

Paul Ogilvie

25,048
4
23
41

I was about to suggest this myself, but a problem occurs once you move past column Z: `?Asc(lcase("aa"))-asc("a")+1` in the immediate-window outputs 1. – eirikdaude Feb 10 '16 at 07:51

iDevlop · Answer 3 · 2016-02-10T08:21:20.993

0

Function getColNum(colLetter As String) As Long
    On Error Resume Next   'will return 0 if letter > last col
    getColNum = Range(colLetter & "1").Column
End Function

edited Feb 10 '16 at 08:21

answered Feb 10 '16 at 08:12

iDevlop

24,841
11
90
149

How to use column letters in VBA

3 Answers3