Get Column Letter of Active Cell in VBA
 

On Saturday, July 31, 2010 10:01:10 PM UTC+5:30, MIG wrote:
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's not possible to, say, select a range based on returning the
current column, or is it?

Is there a way of either

a) returning the column letter or

b) selecting a range based on column number?

Thanks.

Function coletter(col As Integer) As String
arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If col 26 Then
remainder = col Mod 26
devisor = (col - remainder) / 26
If remainder 0 Then
coletter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
Else
coletter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
End If
Else
coletter = Mid(arr, col, 1)
End If

End Function

Get Column Letter of Active Cell in VBA
 

On Saturday, July 31, 2010 10:01:10 PM UTC+5:30, MIG wrote:
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's not possible to, say, select a range based on returning the
current column, or is it?

Is there a way of either

a) returning the column letter or

b) selecting a range based on column number?

Thanks.

Function coletter(col As Integer) As String
arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If col 26 Then
remainder = col Mod 26
devisor = (col - remainder) / 26
If remainder 0 Then
coletter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
Else
coletter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
End If
Else
coletter = Mid(arr, col, 1)
End If

End Function

It's a lot simpler than that...

range(columns(5).address)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Get Column Letter of Active Cell in VBA
 

On Thursday, 17 October 2013 23:23:37 UTC+11, GS wrote:
On Saturday, July 31, 2010 10:01:10 PM UTC+5:30, MIG wrote:

For some reason, activecell.column returns a number, where as

range.select requires a string.



So it's not possible to, say, select a range based on returning the

current column, or is it?



Is there a way of either



a) returning the column letter or



b) selecting a range based on column number?



Thanks.



Function coletter(col As Integer) As String

arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

If col 26 Then

remainder = col Mod 26

devisor = (col - remainder) / 26

If remainder 0 Then

coletter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)

Else

coletter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)

End If

Else

coletter = Mid(arr, col, 1)

End If



End Function



It's a lot simpler than that...



range(columns(5).address)



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Cheers Garry

From your suggestion i came up with this function. Will return only the column letter(s). Not sure if it's perfect but working for me.

Function ColToLetter(colNo As Variant) As String

ColToLetter = Mid(Columns(colNo).Address, 2, (InStr(2, Columns(colNo).Address, ":") - 2))

End Function

Get Column Letter of Active Cell in VBA
 

Cheers back at ya'!

Try this...

Function GetColLabel$(ColNum&)
Split(Replace(Columns(ColNum).Address,"$",""),":") (0)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion