Finding column of a particular cell

I have the following data in columns A to E:
414816 414251 420374 420101 420121

user will be requested to enter the desired 6 digit number and I would like
to establish the related column to use for updating certain cells in that
column.

Can anyone give me codes to achieve this?

Thank you
Prema

To get the column of a particular cell, you can use the column function. For e.g to get the column for cell A1, use Column(A1) which will give you 1.

But I don't think this is what you want. Could you explain a little more, like:
Where will a user enter the 6-digit code. And when/where do you want to know the column the user has entered. Where do you wnat to store the information of the column number the user has used.

- Mangesh

If you prefer, you could also have the user enter their selection using a MsgBox, rather than in a Spreadsheet cell.

Dim ans as String, num as Long
Dim res as Variant
ans = Inputbox("Enter 6 digit number")
if isnumeric(ans) and len(trim(ans)) = 6 then
num = clng(ans)
res = application.Match(num, range("A1:E1"),0)
if not iserror(res) then
col = range("A1")(1, res).Column
else
msgbox "Not found"
end if
End if

--
Regards,
Tom Ogilvy

"Prema" <[email protected]> wrote in message
news:[email protected]...
> I have the following data in columns A to E:
> 414816 414251 420374 420101 420121
>
> user will be requested to enter the desired 6 digit number and I would
like
> to establish the related column to use for updating certain cells in that
> column.
>
> Can anyone give me codes to achieve this?
>
> Thank you
> Prema

Thank you for your help. I had code similar to this and got the number for
the relevant column which I can use to look up to convert to letter of the
column to be used in the following way.
Say if the column where the entered 6 digit number was E then I would like
to update cell E5. That is why I would like the letter rather than the
number. If I use your code can I get to the cell by Range(col &
"5").Select?

Prema

"Tom Ogilvy" wrote:

> Dim ans as String, num as Long
> Dim res as Variant
> ans = Inputbox("Enter 6 digit number")
> if isnumeric(ans) and len(trim(ans)) = 6 then
> num = clng(ans)
> res = application.Match(num, range("A1:E1"),0)
> if not iserror(res) then
> col = range("A1")(1, res).Column
> else
> msgbox "Not found"
> end if
> End if
>
> --
> Regards,
> Tom Ogilvy
>
> "Prema" <[email protected]> wrote in message
> news:[email protected]...
> > I have the following data in columns A to E:
> > 414816 414251 420374 420101 420121
> >
> > user will be requested to enter the desired 6 digit number and I would
> like
> > to establish the related column to use for updating certain cells in that
> > column.
> >
> > Can anyone give me codes to achieve this?
> >
> > Thank you
> > Prema
>
>
>

Thank you for your help. I had code similar to this and got the number for
the relevant column which I can use to look up to convert to letter of the
column to be used in the following way.
Say if the column where the entered 6 digit number was E then I would like
to update cell E5. That is why I would like the letter rather than the
number. If I use your code can I get to the cell by Range(col &
"5").Select?

Prema

"Tom Ogilvy" wrote:

> Dim ans as String, num as Long
> Dim res as Variant
> ans = Inputbox("Enter 6 digit number")
> if isnumeric(ans) and len(trim(ans)) = 6 then
> num = clng(ans)
> res = application.Match(num, range("A1:E1"),0)
> if not iserror(res) then
> col = range("A1")(1, res).Column
> else
> msgbox "Not found"
> end if
> End if
>
> --
> Regards,
> Tom Ogilvy
>
> "Prema" <[email protected]> wrote in message
> news:[email protected]...
> > I have the following data in columns A to E:
> > 414816 414251 420374 420101 420121
> >
> > user will be requested to enter the desired 6 digit number and I would
> like
> > to establish the related column to use for updating certain cells in that
> > column.
> >
> > Can anyone give me codes to achieve this?
> >
> > Thank you
> > Prema
>
>
>

Assuming that E is the matching column

.Cell(5, ColNum)

will give you the equivalent of

.Range("E5")

If you need to select a larger range you can use the .Cell command within .Range:

Range(.Cell(5, ColNum), .Cell(6, ColNum))

is the equivalent of

.Range("E5:E6")

but is obviously that much more dynamic.

In a later posting of the question, Prema agreed that having col was all
that was necessary.

--
Regards,
Tom Ogilvy


"Prema" <[email protected]> wrote in message
news:[email protected]...
> Thank you for your help. I had code similar to this and got the number for
> the relevant column which I can use to look up to convert to letter of the
> column to be used in the following way.
> Say if the column where the entered 6 digit number was E then I would like
> to update cell E5. That is why I would like the letter rather than the
> number. If I use your code can I get to the cell by Range(col &
> "5").Select?
>
> Prema
>
> "Tom Ogilvy" wrote:
>
> > Dim ans as String, num as Long
> > Dim res as Variant
> > ans = Inputbox("Enter 6 digit number")
> > if isnumeric(ans) and len(trim(ans)) = 6 then
> > num = clng(ans)
> > res = application.Match(num, range("A1:E1"),0)
> > if not iserror(res) then
> > col = range("A1")(1, res).Column
> > else
> > msgbox "Not found"
> > end if
> > End if
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Prema" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have the following data in columns A to E:
> > > 414816 414251 420374 420101 420121
> > >
> > > user will be requested to enter the desired 6 digit number and I would
> > like
> > > to establish the related column to use for updating certain cells in
that
> > > column.
> > >
> > > Can anyone give me codes to achieve this?
> > >
> > > Thank you
> > > Prema
> >
> >
> >