Finding column again

[Guest]

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

 

[Tom Ogilvy]

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

 

[Bernie Deitrick]

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

 

[Guest]

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