Converting column number to excel lettered column help!

[laffo16]

Hello everyone, need to make a function which converts a number into the excel formating of the column number. eg.

1 = A

2 = B

27 = AA

28 = AB

sending the numbers and returning the characters. shouldnt be differcult, i'll post the code once im finished, but if anyone has any suggestions for the easiest & fastest way to do this, im all ears.

cheers!

[laffo16]

MsgBox(0, "", _Convert_Num_to_ExcelCol(1))
MsgBox(0, "", _Convert_Num_to_ExcelCol(26))
MsgBox(0, "", _Convert_Num_to_ExcelCol(27))
MsgBox(0, "", _Convert_Num_to_ExcelCol(52))
MsgBox(0, "", _Convert_Num_to_ExcelCol(53))

Func _Convert_Num_to_ExcelCol($CNA_num)
    $CNE_array = StringSplit("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")
    $CNE_int = 0
    While 1
        $CNA_num += -26
        $CNE_int += 1
        If $CNA_num < 1 Then
            If $CNE_int = 1 Then
                $CNA_num += 26
                Return $CNE_array[$CNA_num]
            ElseIf $CNE_int >= 2 Then
                $CNA_num += 26
                Return $CNE_array[$CNE_int -1] & $CNE_array[$CNA_num]
            EndIf
        EndIf
    WEnd
EndFunc

well thats as short as i think i can get it.

Edited October 12, 2008 by laffo16

[Jos]

Think this should be close:

Func _Convert_Num_to_ExcelCol($CNA_num)
    $CNE_array = StringSplit("A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z", ",")
    $CNE_array[0]=""
    $char1 = int(($CNA_num-1)/26)
    $char2 = $CNA_num - $char1*26
    Return $CNE_array[$char1] & $CNE_array[$char2]
EndFunc  ;==>_Convert_Num_to_ExcelCol

[laffo16]

thats the one Jos, thanks m8.

the reason i needed this, is because i delete (or clear) cells in bulk using a copy of a blank cell and paste into a range (A1:B2). i needed number control over the rows:columns which is pasted into.

[Jos]

Excel stops at column 256 = IV

Edited October 13, 2008 by Jos