Function fncToLetters(vintCol As Integer) As String
Dim mstrDigits As String
' Convert a positive number n to its digit representation in base 26.
mstrDigits = ""
Do While vintCol > 0
mstrDigits = Chr(((vintCol - 1) Mod 26) + 65) & mstrDigits
vintCol = Int((vintCol - 1) / 26)
Loop
fncToLetters = mstrDigits
End Function
To reference range of cells you can use Range(Cell1,Cell2), sample:
Sub RangeTest()
Dim testRange As Range
Dim targetWorksheet As Worksheet
Set targetWorksheet = Worksheets("MySheetName")
With targetWorksheet
.Cells(5, 10).Select 'selects cell J5 on targetWorksheet
Set testRange = .Range(.Cells(5, 5), .Cells(10, 10))
End With
testRange.Select 'selects range of cells E5:J10 on targetWorksheet
End Sub
These answers seem strangely convoluted. Unless I'm missing something...if you want to convert numbers to letters, you can just stock them all in an array using a for loop then call on the number associated with that column letter. Like so
For intloop = 1 To 26
colcheck(intloop) = Chr$(64 + intloop)
For lenloop = 1 To 26
colcheck((intloop * 26) + lenloop) = Chr$(64 + intloop) & Chr$(64 + lenloop)
For terloop = 1 To 26
colcheck((intloop * 676) + (lenloop * 26) + terloop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop)
For qualoop = 1 To 26
colcheck((intloop * 17576) + (lenloop * 676) + (terloop * 26) + qualoop) = Chr$(64 + intloop) & Chr$(64 + lenloop) & Chr$(64 + terloop) & Chr$(64 + qualoop)
Next qualoop
Next terloop
Next lenloop
Next intloop
Then just use colcheck(yourcolumnnumberhere) and you will get the column heading associated with that letter (i.e. colcheck(703) = AAA
I really like stackPusher's ConvertToLetter function as a solution. However, in working with it I noticed several errors occurring at very specific inputs due to some flaws in the math. For example, inputting 392 returns 'N\', 418 returns 'O\', 444 returns 'P\', etc.
I reworked the function and the result produces the correct output for all input up to 703 (which is the first triple-letter column index, AAA).
Function ConvertToLetter2(iCol As Integer) As String
Dim First As Integer
Dim Second As Integer
Dim FirstChar As String
Dim SecondChar As String
First = Int(iCol / 26)
If First = iCol / 26 Then
First = First - 1
End If
If First = 0 Then
FirstChar = ""
Else
FirstChar = Chr(First + 64)
End If
Second = iCol Mod 26
If Second = 0 Then
SecondChar = Chr(26 + 64)
Else
SecondChar = Chr(Second + 64)
End If
ConvertToLetter2 = FirstChar & SecondChar
End Function
In case you were looking to transform your column number into a letter:
Function ConvertToLetter(iCol As Integer) As String
Dim iAlpha As Integer
Dim iRemainder As Integer
iAlpha = Int(iCol / 27)
iRemainder = iCol - (iAlpha * 26)
If iAlpha > 0 Then
ConvertToLetter = Chr(iAlpha + 64)
End If
If iRemainder > 0 Then
ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
End If
End Function
This way you could do something like this:
Function selectColumnRange(colNum As Integer, targetWorksheet As Worksheet)
Dim colLetter As String
Dim testRange As Range
colLetter = ConvertToLetter(colNum)
testRange = targetWorksheet.Range(colLetter & ":" & colLetter).Select
End Function
That example function would select the entire column ( i.e. Range("A:A").Select)
Here is a condensed replacement for the ConvertToLetter function that in theory should work for all possible positive integers. For example, 1412 produces "BBH" as the result.
Public Function ColumnNumToStr(ColNum As Integer) As String
Dim Value As Integer
Dim Rtn As String
Rtn = ""
Value = ColNum - 1
While Value > 25
Rtn = Chr(65 + (Value Mod 26)) & Rtn
Value = Fix(Value / 26) - 1
Wend
Rtn = Chr(65 + Value) & Rtn
ColumnNumToStr = Rtn
End Function
Yes! You can use Range.EntireColumn
MSDN
dim column : column = 4
dim column_range : set column_range = Sheets(1).Cells(column).EntireColumn
If you were after a specific column, you could create a hard coded column range with the syntax e.g. Range("D:D")
.
However, I'd use entire column as it provides more flexibility to change that column at a later time.
Worksheet.Columns
provides Range access to a column within a worksheet. MSDN
If you would like access to the first column of the first sheet. You would
call the Columns
function on the worksheet.
dim column_range: set column_range = Sheets(1).Columns(1)
The Columns
property is also available on any Range
MSDN
EntireRow
can also be useful if you have a range for a single cell but would like to reach other cells on the row, akin to a LOOKUP
dim id : id = 12345
dim found : set found = Range("A:A").Find(id)
if not found is Nothing then
'Get the fourth cell from the match
MsgBox found.EntireRow.Cells(4)
end if
Haha, Lovely - let me also include my version of stackPusher's code :). We are using this functionality in C#. Works fine for all Excel ranges.:
public static String ConvertToLiteral(int number)
{
int firstLetter = (((number - 27) / (26 * 26))) % 26;
int middleLetter = ((((number - 1) / 26)) % 26);
int lastLetter = (number % 26);
firstLetter = firstLetter == 0 ? 26 : firstLetter;
middleLetter = middleLetter == 0 ? 26 : middleLetter;
lastLetter = lastLetter == 0 ? 26 : lastLetter;
String returnedString = "";
returnedString = number > 27 * 26 ? (Convert.ToChar(firstLetter + 64).ToString()) : returnedString;
returnedString += number > 26 ? (Convert.ToChar(middleLetter + 64).ToString()) : returnedString;
returnedString += lastLetter >= 0 ? (Convert.ToChar(lastLetter + 64).ToString()) : returnedString;
return returnedString;
}
Source: Stackoverflow.com