Finding column again
 

I posted a message before asking how to find the column within a range which
held a six digit number. Tom Ogilvy kindly provided some codes as below
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

This gives me the column number. But I would like the corresponding letter
of the column. For example If the sis dogit number was in column G, I would
like to update 3 cells in column G (e.g. range("G5").select ActiveCell =
"Done" Range("G7").Select ActiveCell = "OK" and so on) If column was H then
I would update H5, H7 and so on. How can I do this in VB?

Thank you
Prema

Finding column again
 

using the letter instead of the column Number would be the backwards way to
do it.

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
cells(5,col).Value = "Done"
cells(7,col).Value = "OK"
cells(9,col).Value = "And So On"
else
msgbox "Not found"
end if
End if


If you absolutely need the column letter - using col from the existing code

Dim sStr as String
sStr = Left(cells(1,col).Address(0,0), 2 + (col <= 26))
msgbox "Column letter: " & sStr


--
Regards,
Tom Ogilvy



"Prema" wrote in message
...
I posted a message before asking how to find the column within a range
which
held a six digit number. Tom Ogilvy kindly provided some codes as below
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

This gives me the column number. But I would like the corresponding letter
of the column. For example If the sis dogit number was in column G, I
would
like to update 3 cells in column G (e.g. range("G5").select ActiveCell =
"Done" Range("G7").Select ActiveCell = "OK" and so on) If column was H
then
I would update H5, H7 and so on. How can I do this in VB?

Thank you
Prema

Finding column again
 

Prema,

There is no reason to use the column letter once you have the column
number - simply use the Cells(row #, col#) property of the sheet rather than
the Range property. So cell H6 is Cells(6,8)

HTH,
Bernie
MS Excel MVP

"Prema" wrote in message
...
I posted a message before asking how to find the column within a range
which
held a six digit number. Tom Ogilvy kindly provided some codes as below
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

This gives me the column number. But I would like the corresponding letter
of the column. For example If the sis dogit number was in column G, I
would
like to update 3 cells in column G (e.g. range("G5").select ActiveCell =
"Done" Range("G7").Select ActiveCell = "OK" and so on) If column was H
then
I would update H5, H7 and so on. How can I do this in VB?

Thank you
Prema

Finding column again
 

Thank you very much Tom. I was thinking only in one way. Of course what you
have said is best.
Prema

"Prema" wrote:

I posted a message before asking how to find the column within a range which
held a six digit number. Tom Ogilvy kindly provided some codes as below
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

This gives me the column number. But I would like the corresponding letter
of the column. For example If the sis dogit number was in column G, I would
like to update 3 cells in column G (e.g. range("G5").select ActiveCell =
"Done" Range("G7").Select ActiveCell = "OK" and so on) If column was H then
I would update H5, H7 and so on. How can I do this in VB?

Thank you
Prema