Column number
 

Excel 2003 Windows 2000

I want to use the RefEdit control on a form and I want to be able to get the
column number from the cell range the person selects. Just the Column number.

For example if it returns Sheet!$B$3 I want to get the number 2 for the
second column. I can do this in a excel cell directly by using =COLUMN(D7)
I get the number 4.

What I am doing is in a large group of data: Insert a blank row after a
given number of rows or a phrase changes and then averaging all the data in
the given rows above the blank. I presently do this two ways by first having
the user input the first group of averages and then copy that group. Also,
another method I have used is having them insert the column number manually
example 5 for E etc. However, I think it would be best to use the RefEdit
and so they would have that option as sometimes the form is openned and they
can not remember the column and have to close it to scroll the worksheet.

A lot of information you might not need but I like to hear what people are
trying to do.

Thanks for any help you can give..

Column number
 

set rng = Range(Refedit1.Text)
msgbox rng.column

--
Regards,
Tom Ogilvy

"FGM" wrote in message
...
Excel 2003 Windows 2000

I want to use the RefEdit control on a form and I want to be able to get
the
column number from the cell range the person selects. Just the Column
number.

For example if it returns Sheet!$B$3 I want to get the number 2 for the
second column. I can do this in a excel cell directly by using
=COLUMN(D7)
I get the number 4.

What I am doing is in a large group of data: Insert a blank row after a
given number of rows or a phrase changes and then averaging all the data
in
the given rows above the blank. I presently do this two ways by first
having
the user input the first group of averages and then copy that group.
Also,
another method I have used is having them insert the column number
manually
example 5 for E etc. However, I think it would be best to use the RefEdit
and so they would have that option as sometimes the form is openned and
they
can not remember the column and have to close it to scroll the worksheet.

A lot of information you might not need but I like to hear what people are
trying to do.

Thanks for any help you can give..