Change Column Number to Column Title

[aikidokid]

I have used the following to get the last used column in a row, but is there any quick way to transfer this number into the column letters, other than a huge select case or similar. I'm sure there must be, but can't find anything so far
E.G. LastCol = 34
LastCol = AH

For me the LastCol would be "LastCol - 1" as the last column in the row is optional.

LastCol = ActiveSheet.Range("IV1").End(xlToLeft).Column

[Torc]

Found this on another thread here a while ago, can not remember the thread
but it works for me.

Formula is buggy, I have posted a corrected/working version below in Post No 12

Public Function GetColumnLetters(ByVal I As Long) As String
    If I > 0 And I < 27 Then
        GetColumnLetters = Chr$(64 + I)
    ElseIf I > 26 And I < 257 Then
        GetColumnLetters = Chr$(64 + ((I - (I Mod 26)) / 26)) & Chr$(64 + (I Mod 26))
    Else
        GetColumnLetters = ""
    End If
End Function

[aikidokid]

Thanks torc, thats works lovely.

I am getting a Type mismatch on this part though.
Any ideas?

LastCol = ActiveSheet.Range("IV1").End(xlToLeft).Column
ColLetters = GetColumnLetters(LastCol)
If Range("A" & RowNum : ColLetters & LastCol) = "" Then [COLOR=DarkRed]<----------- HERE[/COLOR]
    MsgBox "empty cells exist in this row"
    Exit Sub
Else
    MsgBox "Completed row"
    'change the formatting of the first cell in this row
    Cells(RowNum, 1).Interior.ColorIndex = 1
    Cells(RowNum, 1).Font.ColorIndex = 2
    Cells(RowNum, 1).Font.Bold = True
End If

[Siddharth Rout]

does this help?

Hi

Paste this code in a module...

Function ReturnName(ByVal num As Integer) As String
        ReturnName = Split(Cells(, num).Address, "$")(1)
    End Function

In any cell insert this formula "=ReturnName(34)" . this will give you "AH"

You can also use this formula in vba for example

'this will give you AH
MsgBox ReturnName(34)

Hope this helps...

[aikidokid]

Hi koolsid

I came across this and it seems to work fine.

Public Function GetColumnLetters(ByVal I As Long) As String
    If I > 0 And I < 27 Then
        GetColumnLetters = Chr$(64 + I)
    ElseIf I > 26 And I < 257 Then
        GetColumnLetters = Chr$(64 + ((I - (I Mod 26)) / 26)) & Chr$(64 + (I Mod 26))
    Else
        GetColumnLetters = ""
    End If
End Function

As I have the number of columns that the sheet has used I just use this and it gives me the column letters.

What I am still not too sure about is how to loop through the whole of the row checking if any of the cells are empty.

Once I have done this, then I will send you the sheet ... so far

[Siddharth Rout]

Yes but you need to paste both the functions in the module and my function is a three line code. You make the choice

[aikidokid]

So to call you function in VBA, which is what I am doing it would be..

ColLetters = ReturnName(LastCol - 1)

Did you see my edit on the last post?

PS
By the way, if you don't declare your function as Private or Public, does that mean it will be considered as Public?

[Siddharth Rout]

What I am still not too sure about is how to loop through the whole of the row checking if any of the cells are empty.

Does this help?

Private Sub CommandButton1_Click()
'gives you the Col No of last cell in your range
last_cell_column = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column
 
'count for empty cells
Count = 0
 
'looping thru the cells
For i = 1 To last_cell_column
    'assuming you want to loop thru row 1
    If Cells(1, i).Value = "" Then
        Count = Count + 1
    End If
Next i
 
MsgBox "You have " & Count & "cells empty"
 
End Sub

[Siddharth Rout]

So to call you function in VBA, which is what I am doing it would be..
visual basic code:ColLetters = ReturnName(LastCol - 1)
Did you see my edit on the last post?

If your last column is 34 and you want the name of that column then Why (LastCol - 1)? why not (lastcol)? I can answer the question but i want you to understand the logic... try the code if you want. Also understand what the function ReturnName() does...

On a humorous note try this

Private Sub CommandButton1_Click()
  'last col no
  Colno = 34
  MsgBox "Which is the one that you want: " & ReturnName(Colno) & " or " & ReturnName(Colno - 1)
End Sub

Hope this helps....

[aikidokid]

I should have explained the LastCol - 1
The last column in the row is optional, so the used doesn't have to necessarily enter anything, so if it's empty the records could still be complete.

[Siddharth Rout]

I should have explained the LastCol - 1
The last column in the row is optional, so the used doesn't have to necessarily enter anything, so if it's empty the records could still be complete.

In that case yes,

ColLetters = ReturnName(LastCol - 1) 'which will give you "AG"

This is correct

[Torc]

Just discovered that the formula I posted above has a flaw.

For Column number 52 It returns B@, and for Column 78 It returns C@ etc.

The code below is a modified version of Si's code in http://www.vbforums.com/showthread.php?t=319820

I have tested it for all numbers from 1 to 256 and it retruns the correct column code.

Public Function GetColumnLetters(ByVal I As Long) As String
    If I > 0 And I < 27 Then
        GetColumnLetters = Chr$(64 + I)
    ElseIf I > 26 And I < 257 Then
        GetColumnLetters = Chr$(Int((I - 1) / 26) + 64) & _
                     Chr$(((I - 1) Mod 26) + 1 + 64)
    Else
        GetColumnLetters = ""
    End If
End Function