Convert column letter to number

Question

I get raw data from querys into Excel, and when preforming VLOOKUP's sometimes I have to count or calculate by hand what column I am going to refer to.

I want a calculator were I type in userform textbox ex: "M", and the other textbox will show the correct column number for "M" (13).

My userform looks like this:

I have come up with something like the code below, I dim every letter as an integer and when that is typed in to the textbox it will add each others values.

I don't know how to code the CommandButton1_click "Räkna".

Private Sub CommandButton1_Click()

    'how do i transform letters into numbers here?

End Sub

Sub raknare()

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim f As Integer
Dim g As Integer
Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer
Dim o As Integer
Dim p As Integer
Dim q As Integer
Dim r As Integer
Dim s As Integer
Dim t As Integer
Dim u As Integer
Dim v As Integer
Dim w As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer

Set a = 1
Set b = 2
Set c = 3
Set d = 4
Set e = 5
Set f = 6
Set g = 7
Set h = 8
Set i = 9
Set j = 10
Set k = 11
Set l = 12
Set m = 13
Set n = 14
Set o = 15
Set p = 16
Set q = 17
Set r = 18
Set s = 19
Set t = 20
Set u = 21
Set v = 22
Set w = 23
Set x = 24
Set y = 25
Set z = 26

End Sub

Answer 1

To get info from a dialogue to a variable, you'd do something like

Dim v As Variant
v = Application.InputBox(Prompt:="Letter: ", Title:="Letter", Type:=2)
If CBool(v) Then ' The inputbox returns "false" if cancel is pressed
  ...
EndIf

If you want to use a userform instead, you'd do something like

Dim s As String
s = UserForm1.TextBox1.Text

To get the column number from its name, you can either do something like what's described in this answer.

Or do what I've done in my office, and do the arithmetic yourself:

Answer 2

I solved my problem! But not the way I initially intended. I took some old code i had from a previous project and made it work on this issue to.

I made a sheet called "DATA" and inserted column A with the alphabet A to CW, and next to that i have the corresponding number for each letter 1-100.

Then i made a search function that looks like this:

Sub rakna()

    Dim rSearch As Range
    Dim rFound As Range

    With Sheets("DATA")
        Set rSearch = .Range("A1", .Range("A" & Rows.Count).End(xlUp))

        Set rFound = rSearch.Find(What:=TextBox1.Text, LookIn:=xlValues)

        If rFound Is Nothing Then
            TextBox2.Value = ""
        Else
            TextBox2.Value = rFound.Offset(0, 1).Value


        End If
    End With


End Sub

Now I dont need to calculate the columns no more I can just type the one I need in my textbox!

Answer 3

You could try:

Option Explicit

Sub test()

    Dim Letter As String
    Dim LetterNumber As Long

    Letter = "F"
    LetterNumber = Range(Letter & 1).Column

End Sub