Change Column Number to Column Title

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.

VB Code:

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

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

VB 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$(64 + ((I - (I Mod 26)) / 26)) & Chr$(64 + (I Mod 26))
    Else
        GetColumnLetters = ""
    End If
End Function

:afrog:

Thanks torc, thats works lovely.

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

VB Code:

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

does this help?

Hi

Paste this code in a module...

VB Code:

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

VB Code:

'this will give you AH
MsgBox ReturnName(34)

Hope this helps...

Hi koolsid :wave:

I came across this and it seems to work fine.

VB 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$(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 :thumb:

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

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

VB Code:

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?

Quote:

---

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?

VB Code:

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

Quote:

---

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 ;)

VB Code:

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....

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.

Quote:

---

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,

VB Code:

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

This is correct :)

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.

VB 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