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

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

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

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

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