Clash Royale CLAN TAG#URR8PPP

Column index to number

I get alot of 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.

What I want to do now is 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: https://ibb.co/iug3WU

So far I have only 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 just add each others values.

But I am stuck and dont know how to code the CommandButton1_click "Räkna".

Best regards

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


2 Answers
2

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:

debug.print

UserForm1.TextBox2 = Range(ColName & 1).Column

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!

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.