Есть ли у кого-нибудь функция Excel VBA, которая может возвращать буквенные буквы из числа?
Например, ввод 100 должен возвращать CV
.
Эта функция возвращает букву столбца для заданного номера столбца.
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
код тестирования для столбца 100
Sub Test()
MsgBox Col_Letter(100)
End Sub
(0)
в конец команды Split, если вы хотите сохранить объявление переменной и дополнительную строку кода. например, Col_letter = Split(Cells(1, lngCol).Address(True, False), "$")(0)
Если вы не хотите использовать объект диапазона:
Function ColumnLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function
Что-то, что работает для меня, это:
Cells(Row,Column).Address
Это вернет ссылку формата $AE $1 для вас.
Еще один способ сделать это. Brettdj answer заставлял меня думать об этом, но если вы используете этот метод, вам не нужно использовать альтернативный массив, вы можете перейти непосредственно к строке.
ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")
или может сделать его немного более компактным с этим
ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")
Обратите внимание, что это зависит от того, как вы ссылаетесь на строку 1 в объекте ячейки.
И решение с использованием рекурсии:
Function ColumnNumberToLetter(iCol As Long) As String
Dim lAlpha As Long
Dim lRemainder As Long
If iCol <= 26 Then
ColumnNumberToLetter = Chr(iCol + 64)
Else
lRemainder = iCol Mod 26
lAlpha = Int(iCol / 26)
If lRemainder = 0 Then
lRemainder = 26
lAlpha = lAlpha - 1
End If
ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
End If
End Function
MsgBox Columns( 9347).Address
возвращает . Чтобы возвращать ТОЛЬКО буквенные ( ).Address(,0)),":")(0)
: Split((Columns(
Column Index
).Address(,0)),":")(0)
MsgBox Split((Columns( 2734).Address(,0)),":")(0)
возвращает .
Это доступно с помощью формулы:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
и поэтому также может быть записана как функция VBA по запросу:
Function ColName(colNum As Integer) As String
ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function
ПОСЛЕДНЕЕ ОБНОВЛЕНИЕ. Пожалуйста, проигнорируйте приведенную ниже функцию, @SurasinTancharoen удалось предупредить меня, что она разбита на n = 53
.
Для тех, кто заинтересован, вот другие сломанные значения чуть ниже n = 200
:
КОНЕЦ ОБНОВЛЕНИЯ
Ниже приведена следующая функция:
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
Источник: Как конвертировать номера столбцов Excel в алфавитном порядке
ПРИМЕНЯЕТСЯ НА
Это версия ответа robartsd (с ароматом однострочное решение Jan Wijninckx), используя рекурсию вместо цикла.
Public Function ColumnLetter(Column As Integer) As String
If Column < 1 Then Exit Function
ColumnLetter = ColumnLetter(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function
Я тестировал это со следующими входами:
1 => "A"
26 => "Z"
27 => "AA"
51 => "AY"
702 => "ZZ"
703 => "AAA"
-1 => ""
-234=> ""
код robertsd является элегантным, но для того, чтобы сделать его перспективным, измените объявление n на тип long
Если вам нужна формула, чтобы избежать макросов, вот что работает до столбца 702 включительно
=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)
где A1 - ячейка, содержащая номер столбца, который должен быть преобразован в буквы.
Существует очень простой способ использования Excel: используйте свойство Range.Cells.Address
, таким образом:
strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)
Это вернет адрес нужного столбца в строке 1. Возьмите его 1
:
strCol = Left(strCol, len(strCol) - 1)
Обратите внимание, что это так быстро и мощно, что вы можете вернуть адреса столбцов, которые даже существуют!
Замените lngRow
на нужный номер столбца, используя свойство Selection.Column
!
Это будет работать независимо от того, какой столбец внутри вашей одной строки кода для ячейки, расположенной в строке X, в столбце Y:
Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)
Если у вас есть ячейка с уникальным именем "Cellname":
Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)
Вот простой лайнер, который можно использовать.
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)
Он будет работать только для обозначения столбца с 1 буквой, но он хорош для простых случаев. Если вам нужно работать только для двух буквенных обозначений, вы можете использовать следующее:
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
Это функция, основанная на @DamienFennely answer выше. Если вы дадите мне большие пальцы, дайте ему большие пальцы!: P
Function outColLetterFromNumber(iCol as Integer) as String
sAddr = Cells(1, iCol).Address
aSplit = Split(sAddr, "$")
outColLetterFromNumber = aSplit(1)
End Function
Эта формула даст столбец на основе диапазона (т.е. A1), где диапазон - это одна ячейка. Если задан диапазон нескольких ячеек, он вернет верхнюю левую ячейку. Обратите внимание: обе ссылки на ячейки должны быть одинаковыми:
MID (CELL ( "адрес", A1), 2, SEARCH ( "$", CELL ( "адрес", A1), 2) -2)
Как это устроено:
CELL ("свойство", "диапазон") возвращает определенное значение диапазона в зависимости от используемого свойства. В этом случае адрес ячейки. Свойство address возвращает значение $ [col] $ [row], то есть A1 → $ A $ 1. Функция MID анализирует значение столбца между символами $.
Здесь простая функция в Pascal (Delphi).
function GetColLetterFromNum(Sheet : Variant; Col : Integer) : String;
begin
Result := Sheet.Columns[Col].Address; // from Col=100 --> '$CV:$CV'
Result := Copy(Result, 2, Pos(':', Result) - 2);
end;
Function fColLetter(iCol As Integer) As String
On Error GoTo errLabel
fColLetter = Split(Columns(lngCol).Address(, False), ":")(1)
Exit Function
errLabel:
fColLetter = "%ERR%"
End Function
Вот поздний ответ, просто для упрощенного подхода с использованием Int()
и If
в случае столбцов с 1 символом:
Function outColLetterFromNumber(i As Integer) As String
If i < 27 Then 'one-letter
col = Chr(64 + i)
ElseIf i < 677 Then 'two-letter
col = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
Else 'three-letter
col = Chr(64 + Int(i / 676)) & Chr(64 + Int(i - Int(i / 676) * 676) / 26)) & Chr(64 + i - (Int(i - Int(i / 676) * 676) / 26) * 26))
End If
outColLetterFromNumber = col
End Function
Далее, на основе ответа brettdj, нужно сделать ввод номера столбца опционным. Если ввод номера столбца опущен, функция возвращает букву столбца ячейки, которая вызывает функцию. Я знаю, что это также может быть достигнуто с помощью всего лишь ColumnLetter(COLUMN())
, но я подумал, что было бы хорошо, если бы оно умело это понимало.
Public Function ColumnLetter(Optional ColumnNumber As Long = 0) As String
If ColumnNumber = 0 Then
ColumnLetter = Split(Application.Caller.Address(True, False, xlA1), "$")(0)
Else
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False, xlA1), "$")(0)
End If
End Function
Компромисс этой функции заключается в том, что она будет очень немного медленнее, чем ответ brettdj из-за теста IF
. Но это можно почувствовать, если функция многократно используется в течение очень большого количества раз.
Решение от brettdj работает фантастически, но если вы сталкиваетесь с этим как потенциальное решение по той же причине, что и было, я думал, что предлагаю свое альтернативное решение.
Проблема, с которой я столкнулась, - это прокрутка к определенному столбцу на основе вывода функции MATCH(). Вместо того, чтобы преобразовать номер столбца в параллельную букву столбца, я решил временно переключить стиль ссылки с A1 на R1C1. Таким образом, я мог просто прокрутить до номера столбца, не зациклившись на функции VBA. Чтобы легко переключаться между двумя стилями ссылок, вы можете использовать этот код VBA:
Sub toggle_reference_style()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
End Sub
Это только для REFEDIT... вообще использовать код uphere короткая версия... легко читается и понимается/ он использует poz из $
Private Sub RefEdit1_Change()
Me.Label1.Caption = NOtoLETTER(RefEdit1.Value) ' you may assign to a variable var=....'
End Sub
Function NOtoLETTER(REFedit)
Dim First As Long, Second As Long
First = InStr(REFedit, "$") 'first poz of $
Second = InStr(First + 1, REFedit, "$") 'second poz of $
NOtoLETTER = Mid(REFedit, First + 1, Second - First - 1) 'extract COLUMN LETTER
End Function
Sub GiveAddress()
Dim Chara As String
Chara = ""
Dim Num As Integer
Dim ColNum As Long
ColNum = InputBox("Input the column number")
Do
If ColNum < 27 Then
Chara = Chr(ColNum + 64) & Chara
Exit Do
Else
Num = ColNum / 26
If (Num * 26) > ColNum Then Num = Num - 1
If (Num * 26) = ColNum Then Num = ((ColNum - 1) / 26) - 1
Chara = Chr((ColNum - (26 * Num)) + 64) & Chara
ColNum = Num
End If
Loop
MsgBox "Address is '" & Chara & "'."
End Sub
Письмо столбца из номера столбца можно извлечь, используя формулу, выполнив следующие шаги:
1. Вычислите адрес столбца с помощью формулы ADDRESS
2. Извлечь букву столбца с помощью функции MID и FIND
Пример:
1. АДРЕС (1000,1000,1)
результаты $ALL $1000
2. = MID (F15,2, FIND ( "$", F15,2) -2)
результаты ALL asuming F15 содержит результат шага 1
За один раз мы можем написать
MID (АДРЕС (1000,1000,1), 2, FIND ( "$", адрес (1000,1000,1), 2) -2)
Простой способ получить имя столбца
Sub column()
cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column
End Sub
Я надеюсь, что это поможет =)
как просто преобразовать в число ascii и использовать Chr() для преобразования обратно в букву?
col_letter = Chr (Selection.Column + 96)
Крышка A равна 65, поэтому:
MsgBox Chr(ActiveCell.Column + 64)
Найдено в: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter
CV
в вопросе :(
Здесь другой способ:
{
Sub find_test2()
alpha_col = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,W,Z"
MsgBox Split(alpha_col, ",")(ActiveCell.Column - 1)
End Sub
}