Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

I can't find the specific number you add but you can use Val_ColumnLetter =
CHR(# + 64) function (where # = column number). The 64 is added because the
letter A is the 65th character in the ansicode sequency.

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

Here You go -- Column Letter = CHR(COLNUM + 64)

HTH

Andi

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

This only works for column A to Z. For two characters upto maximim column
IV you would need to split into two parts. It all sounds bit messy. It is a
pity there does not appear to be a built in function in Excel to do this?
--
Cheers
Nigel



"Andibevan" wrote in message
...
Here You go -- Column Letter = CHR(COLNUM + 64)

HTH

Andi

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert
it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters
and
this presents a problem.

Thank you in advance for any help you can provide.

I found the function ConvertFormula and modified it for my needs. It changes
R1C1 tstyle to A1. Since I had all the row and column numbers, it worked
great. Thank you for your help!

See Below:

Function CreateRange( StartRowNum, StartColNum, EndRowNum, EndColNum As
Long)
InputFormula = ConvertRefStyle("R" & StartRowNum & "C" & StartColNum)
InputFormula = InputFormula & ":" & ConvertRefStyle("R" & EndRowNum &
"C" & EndColNum)

Function ConvertRefStyle(InputFormula As String)
ConvertRefStyle = Application.ConvertFormula(Formula:=InputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
End Function



"Nigel" wrote:

This only works for column A to Z. For two characters upto maximim column
IV you would need to split into two parts. It all sounds bit messy. It is a
pity there does not appear to be a built in function in Excel to do this?
--
Cheers
Nigel



"Andibevan" wrote in message
...
Here You go -- Column Letter = CHR(COLNUM + 64)

HTH

Andi

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert
it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters
and
this presents a problem.

Thank you in advance for any help you can provide.

Thanks Andi!

"Andibevan" wrote:

Here You go -- Column Letter = CHR(COLNUM + 64)

HTH

Andi

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

I found the function ConvertFormula and modified it for my needs. It changes
R1C1 tstyle to A1. Since I had all the row and column numbers, it worked
great. Thank you for your help!

See Below:

Function CreateRange( StartRowNum, StartColNum, EndRowNum, EndColNum As
Long)
InputFormula = ConvertRefStyle("R" & StartRowNum & "C" & StartColNum)
InputFormula = InputFormula & ":" & ConvertRefStyle("R" & EndRowNum &
"C" & EndColNum)

Function ConvertRefStyle(InputFormula As String)
ConvertRefStyle = Application.ConvertFormula(Formula:=InputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
End Function



"Andibevan" wrote:

Here You go -- Column Letter = CHR(COLNUM + 64)

HTH

Andi

"Keith" wrote in message
...
Is there a Excel VB function that can take a column number and convert it
to
a column letter? I can write one on my own, but I hope that there is a
built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

Grüezi Keith

Keith schrieb am 17.03.2005

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Try the following function:

Public Function ColLetter(bytColNum As Byte) As String
ColLetter = Replace(Cells(1, bytColNum).Address(0, 0), "1", "")
End Function



Mit freundlichen Grüssen
Thomas Ramel

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

I found the function ConvertFormula and modified it for my needs. It changes
R1C1 style to A1. Since I had all the row and column numbers, it worked
great. Thank you for your help!

See Below:

Function CreateRange( StartRowNum, StartColNum, EndRowNum, EndColNum As
Long)
InputFormula = ConvertRefStyle("R" & StartRowNum & "C" & StartColNum)
InputFormula = InputFormula & ":" & ConvertRefStyle("R" & EndRowNum &
"C" & EndColNum)

Function ConvertRefStyle(InputFormula As String)
ConvertRefStyle = Application.ConvertFormula(Formula:=InputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
End Function



"Thomas Ramel" wrote:

Grüezi Keith

Keith schrieb am 17.03.2005

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Try the following function:

Public Function ColLetter(bytColNum As Byte) As String
ColLetter = Replace(Cells(1, bytColNum).Address(0, 0), "1", "")
End Function



Mit freundlichen Grüssen
Thomas Ramel

--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2000 SP-3]

The following function will return what you want for ALL columns on the
spreadsheet.

Call the function like this:

Sub Test()
Dim strLetters as String
strLetters = ConvertColumnNumber(36)
MsgBox strLetters
End Sub


Public Function ConvertColumnNumber(argColNum) As String
'CALL SUPPLIES A NUMBER; FUNCTION RETURNS EQUIVALENT COLUMN LETTER;
Dim strColumn
If argColNum 256 Then MsgBox "Value exceeds acceptable range (1 to 256)":
End
strColumn = Cells(1, argColNum).Address
AddressConvertColumnNumber = Mid(strColumn, 2, InStr(2, strColumn, "$") - 2)
End Function

Hope this helps.

"Keith" wrote:

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

This is a good one.

"quartz" wrote:

The following function will return what you want for ALL columns on the
spreadsheet.

Call the function like this:

Sub Test()
Dim strLetters as String
strLetters = ConvertColumnNumber(36)
MsgBox strLetters
End Sub


Public Function ConvertColumnNumber(argColNum) As String
'CALL SUPPLIES A NUMBER; FUNCTION RETURNS EQUIVALENT COLUMN LETTER;
Dim strColumn
If argColNum 256 Then MsgBox "Value exceeds acceptable range (1 to 256)":
End
strColumn = Cells(1, argColNum).Address
AddressConvertColumnNumber = Mid(strColumn, 2, InStr(2, strColumn, "$") - 2)
End Function

Hope this helps.

"Keith" wrote:

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

Found this today and modified it to give me a range in A1 notation created
from RC notation. I had the row and column numbers but not the letters. The
Excel VB function is named ConvertFormula.

See Below:
__________________________________________________ _________

Function CreateRange(StartRowNum,StartColNum,EndRowNum,EndC olNum As Long)
InputFormula = ConvertRefStyle("R" & StartRowNum & "C" & StartColNum)
InputFormula = InputFormula & ":" & ConvertRefStyle("R" & EndRowNum &
"C" & EndColNum)
End Function

Function ConvertRefStyle(InputFormula As String)
ConvertRefStyle = Application.ConvertFormula(Formula:=InputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
End Function


Thanks for all your help everyone!
Keith


"Alok" wrote:

This is a good one.

"quartz" wrote:

The following function will return what you want for ALL columns on the
spreadsheet.

Call the function like this:

Sub Test()
Dim strLetters as String
strLetters = ConvertColumnNumber(36)
MsgBox strLetters
End Sub


Public Function ConvertColumnNumber(argColNum) As String
'CALL SUPPLIES A NUMBER; FUNCTION RETURNS EQUIVALENT COLUMN LETTER;
Dim strColumn
If argColNum 256 Then MsgBox "Value exceeds acceptable range (1 to 256)":
End
strColumn = Cells(1, argColNum).Address
AddressConvertColumnNumber = Mid(strColumn, 2, InStr(2, strColumn, "$") - 2)
End Function

Hope this helps.

"Keith" wrote:

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.

I found the function ConvertFormula and modified it for my needs. It changes
R1C1 tstyle to A1. Since I had all the row and column numbers, it worked
great. Thank you for your help!

See Below:

Function CreateRange( StartRowNum, StartColNum, EndRowNum, EndColNum As
Long)
InputFormula = ConvertRefStyle("R" & StartRowNum & "C" & StartColNum)
InputFormula = InputFormula & ":" & ConvertRefStyle("R" & EndRowNum &
"C" & EndColNum)

Function ConvertRefStyle(InputFormula As String)
ConvertRefStyle = Application.ConvertFormula(Formula:=InputFormula, _
fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)
End Function



"quartz" wrote:

The following function will return what you want for ALL columns on the
spreadsheet.

Call the function like this:

Sub Test()
Dim strLetters as String
strLetters = ConvertColumnNumber(36)
MsgBox strLetters
End Sub


Public Function ConvertColumnNumber(argColNum) As String
'CALL SUPPLIES A NUMBER; FUNCTION RETURNS EQUIVALENT COLUMN LETTER;
Dim strColumn
If argColNum 256 Then MsgBox "Value exceeds acceptable range (1 to 256)":
End
strColumn = Cells(1, argColNum).Address
AddressConvertColumnNumber = Mid(strColumn, 2, InStr(2, strColumn, "$") - 2)
End Function

Hope this helps.

"Keith" wrote:

Is there a Excel VB function that can take a column number and convert it to
a column letter? I can write one on my own, but I hope that there is a built
in function to do this. Some properties will only use column letters and
this presents a problem.

Thank you in advance for any help you can provide.