Funkcja konwersji numeru kolumny na literę?

Czy ktoś ma funkcję Excel VBA, która może zwrócić literę (y) kolumny z liczby?

Na przykład wpisanie 100 powinno zwrócić CV.

Ta funkcja zwraca literę kolumny dla danego numeru kolumny.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

kod testowy dla kolumny 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

Jeśli wolisz nie używać obiektu zakresu:

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

Coś, co działa dla mnie to:

Cells(Row,Column).Address 

To zwróci odniesienie do formatu $ AE $ 1.

• Na przykład: MsgBox Columns( 9347 ).Address zwraca .

Aby zwrócić TYLKO litery w kolumnach :Split((Columns(Column Index).Address(,0)),":")(0)

• Na przykład: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) zwraca .

Jeszcze tylko jeden sposób, aby to zrobić. Odpowiedź Brettdj sprawiła, że ​​pomyślałem o tym, ale jeśli używasz tej metody, nie musisz używać tablicy wariantowej, możesz przejść bezpośrednio do łańcucha.

ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")

lub może sprawić, że będzie trochę bardziej zwarty

ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")

Zauważ, że zależy to od tego, czy odwołujesz się do wiersza 1 w obiekcie komórek.

I rozwiązanie wykorzystujące rekursję:

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

Można to uzyskać za pomocą wzoru:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

więc może być również zapisany jako funkcja VBA na żądanie:

Function ColName(colNum As Integer) As String
    ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function

To jest wersja odpowiedzi robartsda (ze smakiem jednowierszowego rozwiązania Jana Wijninckxa ), wykorzystująca rekursję zamiast pętli.

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

Przetestowałem to z następującymi danymi wejściowymi:

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""

Kod robertsda jest elegancki, ale aby był przyszłościowy, zmień deklarację n na typ long

Jeśli chcesz, aby formuła unikała makr, oto coś, co działa do kolumny 702 włącznie

=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)

gdzie A1 to komórka zawierająca numer kolumny do przekonwertowania na litery.

NAJNOWSZA AKTUALIZACJA : Proszę zignorować poniższą funkcję, @SurasinTancharoen zdołał mnie ostrzec, że jest uszkodzony n = 53.
Dla zainteresowanych, poniżej znajdują się inne zepsute wartości n = 200:

Użyj funkcji @brettdj do wszystkich swoich potrzeb. Działa nawet w przypadku ostatniego limitu maksymalnej liczby kolumn programu Microsoft Excel: 16384powinien dawaćXFD

KONIEC AKTUALIZACJI

Poniższa funkcja jest udostępniana przez firmę Microsoft:

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

Źródło: Jak przekonwertować numery kolumn programu Excel na znaki alfabetyczne

DOTYCZY

• Microsoft Office Excel 2007
• Microsoft Excel 2002 Standard Edition
• Microsoft Excel 2000 Standard Edition
• Microsoft Excel 97 Standard Edition

Jest to funkcja oparta na powyższej odpowiedzi @ DamienFennelly . Jeśli dasz mi kciuki do góry, daj mi też kciuki! : P

Function outColLetterFromNumber(iCol as Integer) as String
    sAddr = Cells(1, iCol).Address
    aSplit = Split(sAddr, "$")
    outColLetterFromNumber = aSplit(1)
End Function

Istnieje bardzo prosty sposób korzystania z mocy Excela: Użyj Range.Cells.Addresswłaściwości, w ten sposób:

strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)

Spowoduje to zwrócenie adresu żądanej kolumny w wierszu 1. Weź go z 1:

strCol = Left(strCol, len(strCol) - 1)

Zauważ, że jest tak szybki i potężny, że możesz zwrócić adresy kolumn, które nawet istnieją!

Zastąp lngRowżądany numer kolumny za pomocą Selection.Columnwłaściwości!

Oto prosta wkładka, której można użyć.

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)

Będzie działać tylko dla oznaczenia 1-literowego kolumny, ale jest przyjemny w prostych przypadkach. Jeśli potrzebujesz, aby działał wyłącznie dla oznaczeń 2-literowych, możesz użyć:

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)

To zadziała niezależnie od kolumny wewnątrz jednej linii kodu dla komórki znajdującej się w wierszu X, w kolumnie Y:

Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)

Jeśli masz komórkę z unikalną, zdefiniowaną nazwą „Nazwa komórki”:

Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)

Rozwiązanie firmy brettdj działa fantastycznie, ale jeśli natrafisz na to jako potencjalne rozwiązanie z tego samego powodu, co ja, pomyślałem, że zaoferuję swoje alternatywne rozwiązanie.

Problem, który miałem, polegał na przewijaniu do określonej kolumny na podstawie danych wyjściowych funkcji MATCH (). Zamiast konwertować numer kolumny na równoległą literę kolumny, zdecydowałem się tymczasowo przełączyć styl odniesienia z A1 na R1C1. W ten sposób mogłem po prostu przewinąć do numeru kolumny bez konieczności modyfikowania funkcji VBA. Aby łatwo przełączać się między dwoma stylami referencyjnymi, możesz użyć tego kodu VBA:

Sub toggle_reference_style()

If Application.ReferenceStyle = xlR1C1 Then
  Application.ReferenceStyle = xlA1
Else
  Application.ReferenceStyle = xlR1C1
End If

End Sub

Kontynuując odpowiedź brettdj, tutaj należy wprowadzić opcjonalny numer kolumny. Jeśli pole wprowadzania numeru kolumny zostanie pominięte, funkcja zwraca literę kolumny komórki, która wywołuje funkcję. Wiem, że można to również osiągnąć za pomocą zwykłego ColumnLetter(COLUMN()), ale pomyślałem, że byłoby miło, gdyby mógł to sprytnie to zrozumieć.

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

Wadą tej funkcji jest to, że byłaby ona bardzo, bardzo, bardzo wolniejsza niż odpowiedź brettdj z powodu rozszerzenia IF testu . Ale można to poczuć, jeśli funkcja jest wielokrotnie używana przez bardzo dużą liczbę razy.

Oto późna odpowiedź, tylko dla uproszczonego podejścia przy użyciu Int()i Ifw przypadku kolumn 1-3 znaków:

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

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

Tutaj prosta funkcja w Pascalu (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;

Ta formuła poda kolumnę na podstawie zakresu (tj. A1 ), gdzie zakres to pojedyncza komórka. Jeśli podany zostanie zakres obejmujący wiele komórek, zwróci on lewą górną komórkę. Uwaga: oba odwołania do komórek muszą być takie same:

MID (CELL ("adres", A1 ), 2, SEARCH ("$", CELL ("adres", A1 ), 2) -2)

Jak to działa:

KOMÓRKA („właściwość”, „zakres”) zwraca określoną wartość zakresu w zależności od użytej właściwości. W tym przypadku adres komórki. Właściwość address zwraca wartość $ [col] $ [wiersz], tj. A1 -> $ A $ 1. Funkcja MID analizuje wartość kolumny między symbolami $.

Łatwy sposób na uzyskanie nazwy kolumny

Sub column()

cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column

End Sub

Mam nadzieję, że to pomoże =)

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

Więc spóźniłem się na imprezę tutaj, ale chcę dać kolejną odpowiedź, której nikt inny jeszcze nie rozwiązał, która nie dotyczy tablic. Możesz to zrobić za pomocą prostej manipulacji na strunach.

Function ColLetter(Col_Index As Long) As String

    Dim ColumnLetter As String

    'Prevent errors; if you get back a number when expecting a letter, 
    '    you know you did something wrong.
    If Col_Index <= 0 Or Col_Index >= 16384 Then
        ColLetter = 0
        Exit Function
    End If

    ColumnLetter = ThisWorkbook.Sheets(1).Cells(1, Col_Index).Address     'Address in $A$1 format
    ColumnLetter = Mid(ColumnLetter, 2, InStr(2, ColumnLetter, "$") - 2)  'Extracts just the letter

    ColLetter = ColumnLetter
End Sub

Po wprowadzeniu danych w formacie $A$1użyj Midfunkcji, zacznij od pozycji 2, aby uwzględnić pierwszą pozycję $, a następnie znajdź miejsce, w którym $pojawia się druga w ciągu InStr, a następnie odejmij 2, aby uwzględnić tę pozycję początkową.

Daje to korzyść w postaci możliwości dostosowania do całej gamy możliwych kolumn. Dlatego ColLetter(1)zwraca "A" i ColLetter(16384)zwraca "XFD", która jest ostatnią możliwą kolumną dla mojej wersji Excela.

Literę kolumny z numeru kolumny można wyodrębnić za pomocą formuły, wykonując następujące kroki
1. Oblicz adres kolumny za pomocą formuły ADDRESS
2. Wyodrębnij literę kolumny za pomocą funkcji MID i FIND

Przykład:
1. ADDRESS (1000,1000,1)
wyniki $ ALL 1000 $
2 . = MID (F15,2, FIND ("$", F15,2) -2)
wyniki WSZYSTKO zakładając, że F15 zawiera wynik z kroku 1 Za

jednym razem możemy wpisać
MID (ADRES (1000,1000,1), 2, ZNAJDŹ („$”; ADRES (1000,1000,1); 2) -2)

to jest tylko dla REFEDIT ... ogólnie używaj tam kodu w krótkiej wersji ... łatwy do odczytania i zrozumienia / używa poz z $

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

Czapka A to 65, więc:

MsgBox Chr(ActiveCell.Column + 64)

Znalezione w: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter

co powiesz na konwersję na liczbę ascii i użycie Chr () do konwersji z powrotem na literę?

col_letter = Chr (Selection.Column + 96)

Oto inny sposób:

{

      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

}