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
Re: Change Column Number to Column Title
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:
Re: Change Column Number to Column Title
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
Re: Change Column Number to Column Title
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...
Re: Change Column Number to Column Title
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:
Re: Change Column Number to Column Title
Yes but you need to paste both the functions in the module and my function is a three line code. You make the choice :)
Re: Change Column Number to Column Title
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?
Re: Change Column Number to Column Title
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
Re: Change Column Number to Column Title
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....
Re: Change Column Number to Column Title
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.
Re: Change Column Number to Column Title
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 :)
Re: Change Column Number to Column Title
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