Error converting a column number to letters

[insomniac53]

I have been using this function for some time to convert a column number to letters. It should take any number and return the correct letters, even double letters, e.g. AH etc. It works, but there is at least one number which causes it to fail - that number is 36.
Here is the function:

Public Function ntc(ByVal iCol As Long) As String 'Number To String (returns ANY column LETTER)
    If iCol Then ntc = ntc((iCol - 1) \ 26) & Chr(65 + (iCol - 1) Mod 26)
End Function

When iCol is 35, function returns "AI" and when iCol is 37, function returns "AK" but when iCol is 36 function is undefined.
Can anyone see why it fails when iCol is 36 (and probably multiples of 36, too) ?

Thank you.

 

[mole999]

i've taken it to LZ 338 and no problem as supplied

is AJ defined as something ?

 

[insomniac53]

Thank you for replying. I've tested it myself again and you are right, there's no error. However, in my Debug=>Watch line, the function return string shows as NULL. I'm going to mark this as solved becasue the problem is outside the immediate function and in my calling code. VBA is unforgiving and I suspect it might be a simple integer/long parameter call issue, but will investigate.

Hmm... can't see how to mark this as Solved...

 

[Peter_SSs]

If you are interested, I think this non-recursive function should also do the job.

Public Function ntc(ByVal iCol As Long) As String
    ntc = Replace(Cells(1, iCol).Address(0, 0), 1, "")
End Function

.. or you could also use this line in the function.

ntc = Split(Cells(1, iCol).Address, "$")(1)

 

[insomniac53]

Both work perfectly. Might be safer than the recursive version. Thank you.

 

[cnestg8r]

Or you could skip VBA. This works through 702 columns, but could easily be extended to cover through XFD(16384)..

=IF(ROUNDDOWN((COLUMN()-1)/26,0)=0,"",CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64))&CHAR(MOD(COLUMN()-1,26)+65)

 

[insomniac53]

Thanks - interesting. All these replies are good. I need VBA as it's part of larger code but 702 columns would be plenty!

 

[Rick Rothstein]

Or you could skip VBA. This works through 702 columns, but could easily be extended to cover through XFD(16384)..

=IF(ROUNDDOWN((COLUMN()-1)/26,0)=0,"",CHAR(ROUNDDOWN((COLUMN()-1)/26,0)+64))&CHAR(MOD(COLUMN()-1,26)+65)

If you are going to use a formula for this, why not use this much simpler one (it works all the way up to Column XFD as is)...

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,"")

 

[cnestg8r]

Great reply Rick. Certainly easier and faster. Thanks

 

[Peter_SSs]

Both work perfectly. Might be safer than the recursive version. Thank you.

You are very welcome.