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

Finding column of a particular 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" wrote in message
...
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

Finding column of a particular cell
 

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" wrote in message
...
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

Finding column of a particular cell
 

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" wrote in message
...
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

Finding column of a particular cell
 

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

--
Regards,
Tom Ogilvy


"Prema" wrote in message
...
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" wrote in message
...
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