Hat jemand eine Excel-VBA-Funktion, die die Spaltenbuchstaben aus einer Zahl zurückgeben kann?
Wenn Sie zum Beispiel 100 eingeben, sollte CV
zurückgegeben werden.
Diese Funktion gibt den Spaltenbuchstaben für eine bestimmte Spaltennummer zurück.
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
Prüfcode für Spalte 100
Sub Test()
MsgBox Col_Letter(100)
End Sub
Wenn Sie kein Bereichsobjekt verwenden möchten:
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
Was für mich funktioniert, ist:
Cells(Row,Column).Address
Dadurch wird die Referenz für das $ AE $ 1-Format zurückgegeben.
Nur noch ein Weg, dies zu tun. Brettdjs Antwort Ich musste daran denken, aber wenn Sie diese Methode verwenden, müssen Sie kein variantes Array verwenden.
ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")
oder kann es damit etwas kompakter machen
ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")
Beachten Sie, dass dies davon abhängt, dass Sie Zeile 1 im Zellenobjekt referenzieren.
Und eine Lösung mit Rekursion:
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
Dies ist über eine Formel verfügbar:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
und kann auch wie gewünscht als VBA-Funktion geschrieben werden:
Function ColName(colNum As Integer) As String
ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function
LATEST UPDATE: Bitte ignorieren Sie die unten stehende Funktion. @SurasinTancharoen hat mich informiert, dass bei n = 53
ein Fehler aufgetreten ist.
Für diejenigen, die daran interessiert sind, sind hier andere gebrochene Werte unterhalb von n = 200
:
ENDE DER AKTUALISIERUNG
Die folgende Funktion wird von Microsoft bereitgestellt:
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
Quelle: So konvertieren Sie Excel-Spaltennummern in alphabetische Zeichen
GILT FÜR
robertsds Code ist elegant, aber um es zukunftssicher zu machen, ändern Sie die Deklaration von n in type long
Für den Fall, dass Sie eine Formel zur Vermeidung von Makros benötigen, können Sie die Spalte 702 (einschließlich) verwenden
=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)
dabei ist A1 die Zelle, die die Spaltennummer enthält, die in Buchstaben umgewandelt werden soll.
Dies ist eine Version von robartsds Antwort (mit dem Geschmack von Jan Wijninckxs Einzeilenlösung ), wobei Rekursion anstelle einer Schleife verwendet wird.
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
Ich habe dies mit den folgenden Eingaben getestet:
1 => "A"
26 => "Z"
27 => "AA"
51 => "AY"
702 => "ZZ"
703 => "AAA"
-1 => ""
-234=> ""
Es gibt eine sehr einfache Methode zur Verwendung von Excel-Power: Verwenden Sie die Range.Cells.Address
-Eigenschaft auf folgende Weise:
strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)
Dies gibt die Adresse der gewünschten Spalte in Zeile 1 zurück. Nehmen Sie den 1
mit:
strCol = Left(strCol, len(strCol) - 1)
Beachten Sie, dass es so schnell und leistungsstark ist, dass Sie Spaltenadressen zurückgeben können, die sogar existieren!
Ersetzen Sie lngRow
durch die Selection.Column
-Eigenschaft für die gewünschte Spaltennummer!
Dies ist eine Funktion basierend auf @ DamienFennellys Antwort oben. Wenn du mir einen Daumen nach oben gibst, gib ihm auch einen Daumen nach oben! : P
Function outColLetterFromNumber(iCol as Integer) as String
sAddr = Cells(1, iCol).Address
aSplit = Split(sAddr, "$")
outColLetterFromNumber = aSplit(1)
End Function
Dies funktioniert unabhängig von der Spalte in Ihrer einen Codezeile für die Zelle, die sich in Zeile X in Spalte Y befindet:
Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)
Wenn Sie eine Zelle mit einem eindeutigen definierten Namen "Zellname" haben:
Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)
Hier ist ein einfacher Liner, der verwendet werden kann.
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)
Es funktioniert nur für eine 1-Buchstaben-Spaltenbezeichnung, ist aber für einfache Fälle Nizza. Wenn Sie es benötigen, um ausschließlich mit 2 Buchstaben zu arbeiten, können Sie Folgendes verwenden:
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
Diese Formel ergibt die Spalte basierend auf einem Bereich (d.h. A1 ), wobei der Bereich eine einzelne Zelle ist. Wenn ein Bereich mit mehreren Zellen angegeben ist, wird die obere linke Zelle zurückgegeben. Beachten Sie, dass beide Zellverweise gleich sein müssen:
MID (CELL ("Adresse", A1 ), 2, SUCHE ("$", CELL ("Adresse", A1 ), 2) -2)
Wie es funktioniert:
CELL ("property", "range") gibt abhängig von der verwendeten Eigenschaft einen bestimmten Wert des Bereichs zurück. In diesem Fall gibt die Zellenadresse ... die Eigenschaft adresse einen Wert $ [col] $ [Zeile] zurück, d. H. A1 -> $ A $ 1 . Die MID-Funktion analysiert den Spaltenwert zwischen den $ -Symbolen.
Hier ist eine späte Antwort, nur für einen vereinfachten Ansatz, der Int()
und If
im Fall von 1-3-Zeichen-Spalten verwendet:
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
Die Lösung von brettdj funktioniert fantastisch, aber wenn Sie aus dem gleichen Grund wie ich auf eine mögliche Lösung stoßen, dachte ich, ich würde meine alternative Lösung anbieten.
Das Problem, das ich hatte, war das Scrollen zu einer bestimmten Spalte basierend auf der Ausgabe einer MATCH () - Funktion. Anstatt die Spaltennummer in den Spaltenbuchstaben parallel umzuwandeln, habe ich den Referenzstil vorübergehend von A1 auf R1C1 umgeschaltet. Auf diese Weise konnte ich einfach zur Spaltennummer scrollen, ohne mit einer VBA-Funktion arbeiten zu müssen. Um zwischen den beiden Referenzstilen problemlos umzuschalten, können Sie diesen VBA-Code verwenden:
Sub toggle_reference_style()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
End Sub
Hier eine einfache Funktion in 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;
Als Antwort auf die Antwort von brettdj ist hier die Eingabe der Spaltennummer optional. Wenn die Eingabe der Spaltennummer weggelassen wird, gibt die Funktion den Spaltenbuchstaben der Zelle zurück, die die Funktion aufruft. Ich weiß, dass dies auch nur mit ColumnLetter(COLUMN())
erreicht werden kann, aber ich dachte, es wäre schön, wenn es das klug verstehen könnte.
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
Der Kompromiss dieser Funktion ist, dass sie aufgrund des IF
-Tests sehr etwas langsamer als brettdjs Antwort wäre. Dies ist jedoch spürbar, wenn die Funktion sehr oft wiederholt verwendet wird.
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
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
Also bin ich zu spät zur Party hier, aber ich möchte eine weitere Antwort beisteuern, an die sich bisher noch niemand gerichtet hat, die keine Arrays umfasst. Sie können dies mit einer einfachen String-Manipulation tun.
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
Nachdem Sie die Eingabe im Format $A$1
vorgenommen haben, verwenden Sie die Funktion Mid
. Beginnen Sie an Position 2, um den ersten $
zu berücksichtigen. Dann finden Sie, wo der zweite $
in der Zeichenfolge mit InStr
angezeigt wird, und subtrahieren Sie 2, um den Start zu berücksichtigen Position.
Dadurch haben Sie den Vorteil, dass Sie für alle möglichen Säulen anpassbar sind. Daher gibt ColLetter(1)
"A" zurück und ColLetter(16384)
gibt "XFD" zurück, die letzte Spalte für meine Excel-Version.
Einfache Möglichkeit, den Spaltennamen zu erhalten
Sub column()
cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column
End Sub
Ich hoffe es hilft =)