More question on Column Number

[FGM]

Is there a way to get the Beginning and Ending Column number (not count)
from a range when using the RefEdit on a form? I know you could put two
RefEdit's on the form and ask for the beginning and then the ending but users
might frown on having to do that... I would.

Thanks all help is most appreciated... someday I will have much knowledge.

The following was my question before followed by the answer.
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..




Was this post helpful to you?
Reply Top





Tom Ogilvy 10/20/2005 11:38 AM PST





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

--
Regards,
Tom Ogilvy

[Bob Phillips]

You can get an offset column like



--

HTH

RP
(remove nothere from the email address if mailing direct)


"FGM" <FGM@discussions.microsoft.com> wrote in message
news:989E12B6-3455-4B6B-A50C-79BECD28879A@microsoft.com...
> Is there a way to get the Beginning and Ending Column number (not count)
> from a range when using the RefEdit on a form? I know you could put two
> RefEdit's on the form and ask for the beginning and then the ending but
users
> might frown on having to do that... I would.
>
> Thanks all help is most appreciated... someday I will have much
knowledge.
>
> The following was my question before followed by the answer.
> 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..
>
>
>
>
> Was this post helpful to you?
> Reply Top
>
>
>
>
>
> Tom Ogilvy 10/20/2005 11:38 AM PST
>
>
>
>
>
> set rng = Range(Refedit1.Text)
> msgbox rng.column
>
> --
> Regards,
> Tom Ogilvy
>

[Bob Phillips]

You can get an offset column like

Range("B3").Columns(2).Column

which gives 3

The first and last column number of a range like

Range("B3").Column

Range("E7:M19")(Range("E7:M19").Count).column

--

HTH

RP
(remove nothere from the email address if mailing direct)


"FGM" <FGM@discussions.microsoft.com> wrote in message
news:989E12B6-3455-4B6B-A50C-79BECD28879A@microsoft.com...
> Is there a way to get the Beginning and Ending Column number (not count)
> from a range when using the RefEdit on a form? I know you could put two
> RefEdit's on the form and ask for the beginning and then the ending but
users
> might frown on having to do that... I would.
>
> Thanks all help is most appreciated... someday I will have much
knowledge.
>
> The following was my question before followed by the answer.
> 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..
>
>
>
>
> Was this post helpful to you?
> Reply Top
>
>
>
>
>
> Tom Ogilvy 10/20/2005 11:38 AM PST
>
>
>
>
>
> set rng = Range(Refedit1.Text)
> msgbox rng.column
>
> --
> Regards,
> Tom Ogilvy
>