converting letters to numbers

T

trav

I have a function that runs through data and breaks up, sums, and groups
different information (big thanks to Hans for helping me out with it).

Now i would like to make it more user friendly. Before i had the
column numbers hard coded into the formula, but now people in the
office are starting to use the function for different data sets where
the columns are different. I created a simple form were people can
enter the number of the column.
row start = 2
group by col = 5
sum value of col = 11

the problem is that people have a hard time figuring out that column K
is 11, and would much rather type in K into the form then 11.

is there a quick and easy way (or possibly a built in function in
excel) that i can do this.
I realize i could build a giant Select Case statement, but i just think
there has to be a better way.

Another quick question: is there a way to create an input rule in excel
forms similar to Access, where i can limit what a user inputs, for
instance, the row text box can only be numbers, and the column can only
be letters.

thanks
 
V

vezerid

I must go now and I have not seen the previous post, but it seems to me
you need the INDIRECT function. For example, if cell K1 contains the
number 2 (row start) and you want to sum 11 consicutive cells or
something similar (11 in K2), then

=SUM(INDIRECT("A"&K1&":A"&K1+K2-1))

will calculate the sum of the range A2:A12

Is this what you need?

Kostis Vezerides
 
D

Dave Peterson

You could ask them to point at a cell to use as the starting point.

dim myCellToStart as range
dim RowStart as long
dim SumValueOfCol as long

set mycelltostart = nothing
on error resume next
set mycelltostart=application.inputbox(Prompt:="CLick on a cell",type:=8)
on error goto 0

if mycelltostart is nothing then
'user hit cancel
exit sub '???
end if

RowStart = mycelltostart.row
sumvalueofCol = mycelltostart.column


If you need two columns--one to group by and one to sum, you can ask two
questions and just extract the column from each answer.
 
D

Dave Peterson

And there is no mask like this in excel--but you could do your own checking.

ps. Instead of asking multiple questions, it's sometimes easier to just display
a userform and allow the user to fill out a couple of prompts and then continue.
 
T

trav

Thanks for help. I ll give the question answer thing a try,

As of now i do have a form, and it works great.
at first I had two different combo boxes and some check boxes that
controlled different configurations and options for layout, but now too
many people are using it for many different spreadsheets.
So I reconfigured the form so it now has three text boxes.
Example
Row Start = 2 ' the 2 is in the text box, and it tells the function
what row to start on
Group by Col = 5 ' this is the column that the info is grouped by
Value Col = 11 ' this is the column that they value is subtotaled for
each group

so what I would like is for the user to be able to enter
Row Start = 2
Group by Col = E 'use a letter instead of number
Value Col = K ' use a letter instead of number

i just figured that was the simplest way.
The other way would be to grab the names from the first row, which are
the column names, and create a drop down selection box.

This would be the preferable way, but I am not that experienced with
excel.

If I were using php I would take the values and create an array as I
ran through the columns of row 1,
Then I would create loop that put them into an html selection box with
the value being the column number, and the text would be the column
name.

Is this possible in excel? If so that would be the best way

Otherwise just being able to convert a letter to a number would be
fine.
 
D

Dave Peterson

Maybe just adding a couple of refedit controls would work for you. They work a
lot like application.inputbox(type:=8).
 
T

trav

i don't really understand application.inputbox(type:=8)

i don't know much about excel and vb, besides the normal stuff, so all
the functions i do are pretty much form based. how do you utilize this
input box, and how can i apply it to input the column number?
 
D

Dave Peterson

application.inputbox is a quick way to get a range from a user. It allows the
user to use the mouse to point and click.

If you create a simple test macro, you may find it easier to understand.

Option Explicit
Sub testme()

Dim myCellToStart As Range
Dim RowStart As Long
Dim SumValueOfCol As Long

Set myCellToStart = Nothing
On Error Resume Next
Set myCellToStart = Application.InputBox(Prompt:="CLick on a cell", Type:=8)
On Error GoTo 0

If myCellToStart Is Nothing Then
'user hit cancel
Exit Sub '???
End If

RowStart = myCellToStart.Row
SumValueOfCol = myCellToStart.Column

MsgBox RowStart & vbLf & SumValueOfCol
End Sub
 
T

trav

THANKS, that works pretty nice.

just one more question,
in a combo box, is it possible to have a name that is different then
the value.

so if i have a drop down list, say

company
quantity
unit price
sale

can i have a different value associated with them like

company = 1
quantity = 2
unit price = 3
sale = 4

and if so, how do i set that in the form.
 
D

Dave Peterson

Yep.

I'm assuming that the combobox is still on that userform.

If that's correct, take a look at the .listindex property of the combobox.

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.ListIndex
End Sub

The first item on the list will have an index of 0, so...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.ComboBox1.ListIndex + 1
End Sub

If you have something weirder(?) happening, you could always build a table on
another worksheet and use =vlookup() to get the number you want.
 
Top