wake-up-neo.net

Excel-Bereiche mit Spaltennummern in vba erstellen?

Wie ist es möglich, einen Bereich in vba mit der Spaltennummer anstatt mit dem Buchstaben zu erstellen?

36
mezamorphic

Im Folgenden finden Sie zwei Lösungen zum Auswählen des Bereichs A1. 

Cells(1,1).Select '(row 1, column 1) 
Range("A1").Select

Schauen Sie sich auch diesen Link an.

Es wird dringend empfohlen, dass Sie Range anstelle von Cells verwenden, um mit .__ zu arbeiten. Zellen und Zellgruppen. Es macht Ihre Sätze viel klarer und Sie müssen sich nicht daran erinnern, dass Spalte AE Spalte 31 ist.

Die einzige Zeit, in der Sie Cells verwenden werden, ist, wenn Sie alle .__ auswählen möchten. die Zellen eines Arbeitsblatts. Zum Beispiel: Cells.Select Um alle auszuwählen Zellen und leeren Sie dann alle Zellen mit Werten oder Formeln, die Sie verwenden werden: Cells.ClearContents

-

"Zellen" ist besonders nützlich, wenn Sie Bereiche dynamisch einstellen und Durchlaufen von Bereichen mit Hilfe von Zählern. Definieren von Bereichen mit Buchstaben als Spaltennummern können auf kurze Sicht transparenter sein, Ihre Anwendung wird dadurch jedoch starrer, da es sich um "harte kodierte" Darstellungen handelt - nicht um dynamische.

Danke an Kim Gysen

30
CustomX

Um den Zellbereich zu referenzieren, können Sie Range (Cell1, Cell2) verwenden, 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

enter image description here

49
dee

Range.EntireColumn

Ja! Sie können Range.EntireColumn MSDN verwenden

dim column : column = 4

dim column_range : set column_range = Sheets(1).Cells(column).EntireColumn

Bereich ("ColumnName: ColumnName")

Wenn Sie nach einer bestimmten Spalte suchen, können Sie einen hartcodierten Spaltenbereich mit der Syntax erstellen, z. Range("D:D").

Ich würde jedoch die gesamte Spalte verwenden, da dies mehr Flexibilität bietet, um diese Spalte zu einem späteren Zeitpunkt zu ändern.

Arbeitsblatt.Spalten

Worksheet.Columns Bietet Range-Zugriff auf eine Spalte in einem Arbeitsblatt. MSDN

Wenn Sie auf die erste Spalte des ersten Blattes zugreifen möchten. Sie würden die Funktion Columns im Arbeitsblatt aufrufen.

dim column_range: set column_range = Sheets(1).Columns(1)

Die Eigenschaft Columns ist auch für alle Range MSDN verfügbar

EntireRow kann auch nützlich sein, wenn Sie einen Bereich für eine einzelne Zelle haben, aber andere Zellen in der Zeile erreichen möchten, ähnlich einem 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
7
James Forbes

Hier ist ein kompakter Ersatz für die ConvertToLetter-Funktion, die theoretisch für alle möglichen positiven Ganzzahlen funktionieren sollte. Zum Beispiel erzeugt 1412 "BBH" als Ergebnis.

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
3
Darin

Falls Sie Ihre Spaltennummer in einen Buchstaben umwandeln wollten:

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

Auf diese Weise könnten Sie so etwas tun:

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

Diese Beispielfunktion würde die gesamte Spalte (d. H. Bereich ("A: A") auswählen. Auswählen)

Quelle: http://support.Microsoft.com/kb/833402

2
stackPusher

Diese Antworten scheinen seltsam verschlungen zu sein. Wenn mir etwas fehlt ... Wenn Sie Zahlen in Buchstaben konvertieren möchten, können Sie sie einfach in einem Array mit einer for-Schleife speichern und dann die Nummer anrufen, die diesem Spaltenbuchstaben zugeordnet ist. 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

Verwenden Sie dann einfach colcheck (Ihre Spaltennummer) und Sie erhalten die mit diesem Buchstaben verknüpfte Spaltenüberschrift (d. H. Colcheck (703) = AAA)

1
Chahe

Die ConvertToLetter-Funktion von stackPusher gefällt mir wirklich als Lösung. Bei der Arbeit sind jedoch einige Fehler aufgetreten, die bei bestimmten Eingaben aufgrund einiger Fehler in der Mathematik auftraten. Zum Beispiel gibt die Eingabe 392 'N \' zurück, 418 gibt 'O \' zurück, 444 gibt 'P \' usw. zurück.

Ich habe die Funktion überarbeitet und das Ergebnis liefert die korrekte Ausgabe für alle Eingaben bis 703 (dies ist der erste Index aus drei Buchstaben, 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
1
JohannesKepler

Haha, Lovely - lass mich auch meine Version von stackPusher's Code einfügen :) Wir verwenden diese Funktionalität in C #. Funktioniert gut für alle Excel-Bereiche .:

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;
}
0