Wie erstelle ich eine variable intelligente Tabelle?

Eine Bestandsauswertung wird in Excel(2010) ausgegeben und tägl. in meine zu analysierende Tabelle in einem Blatt (Abl.Schleifpuffer) abgelegt bzw. überschrieben. In diesem Blatt soll mittels VBA eine intelligente Tabelle erstellt werden.

Per Aufzeichnung klappt das ganz gut, nur gibt es da paar Haken:

1. sollte sich die Auswertung (Quelle) ändern aufgrund Programmanpassungen, verschieben sich auch die Zellen. So beginnt sie nicht von A11 sondern evtl. von B12

2. erstelle ich eine intelligente Tabelle, so gibt mir das Makro immer die Größe von der zuerst erstellten Tabelle "$A$11:$N$853" wieder, sodass bei der nächsten Auswertung die Zeilen ab 854 gar nicht mit einfließen. D.h. die Auswertung wird tägl. gemacht und die Länge ist definitiv variabel.

Wie bekomme ich Excel dazu das er automatisch erkennt "Ab Spalte A und Zeile 11 beginnt die Tabelle" und endet bei Spalte A und Zeile 999.

Derzeit ist die Aufzeichnung so verlaufen:

    Sub konvertiere_Abl_als_Tabelle()
'
' konvertiere_Abl_als_Tabelle Makro
'

'
    Sheets("Abl. Schleifpuffer").Select
    Range("A11").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$11:$N$853"), , xlYes).Name = _
        "Abl.Schleifpuffer"
    Range("Abl.Schleifpuffer[#All]").Select
End Sub

Für eure Unterstützung wäre ich sehr dankbar.

Answer 1

[Suboptimierer]

Was sind denn die Kriterien dafür, wo die Tabelle beginnt?

Schaden könnte es der Übersicht halber nicht, die Startzelle im Sheet zu benamsen (Bsp. "TabelleStart")

Dim rStart as Range
Set rStart = Range("TabelleStart")
...
rStart.Select
...

Du kannst natürlich auch probieren, die Startzelle zu finden:

Set rStart = Range("A1").End(xlDown).Offset(1, 0).End(xlDown)

Oder mit einer Schleife

For i=2 To 10000
  If Cells(i-1,1).Value = "" and Cells(i,1).Value <> "" Then
    Set rStart = Cells(i,1)
    Exit For
  End If
Next

Die folgende Zeile könntest du versuchen, wie folgt zu flexibilisieren:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$11:$N$853"), , xlYes).Name = _
 "Abl.Schleifpuffer"

...Add(xlSrcRange, Range(rStart,Selection), , xlYes).Name = _
 "Abl.Schleifpuffer"

Comments

[Phila86]

Kein Problem, dann frage ich wieder in die Runde ^^

[Phila86]

Verstehe. Ok, dann hab recht vielen Dank :)

[Suboptimierer]

@Phila86

Bitteschön!

[Phila86]

Eine kleine Bitte hätte ich noch, da ich nichts dazu finde:

Wie würde der Befehl dann lauten wenn ich die intelligente Tabelle wieder in einen normalen Bereich konvertieren möchte?

Ich wüsste es nur manuell.

[Suboptimierer]

@Phila86

Kurz gesagt: Mit "intelligenten Tabellen" kenne ich mich nicht aus. Das ist die einzige Zeile, in die ich mich einarbeiten müsste ;P

[Phila86]

Die Kriterien dafür sind das er die Tabelle anhand der "Betriebsmittel"-Nr. erkennt.

Nur kommt diese Benennung 2x in Spalte A vor, sodass er bestenfalls noch die benachbarte Spalte "Benennung FHMI" erkennen sollte.

oder man startet ab Zelle A5 um das zu umgehen.

[Suboptimierer]

@Phila86

  Dim i, j As Integer
  Dim rStart As Range
  Set rStart = Nothing
  For i = 1 To 100
    For j = 1 To 100
      If Cells(i, j).Value = "Betriebsmittel" And Cells(i, j + 1).Value = "Benennung FHMI" Then
        Set rStart = Cells(i, j)
        Exit For
      End If
    Next
  Next
  If rStart Is Nothing Then Debug.Print "Start nicht gefunden": Exit Sub

[Phila86]

@Suboptimierer

Habe vielleicht noch Kriterien vergessen:

1. In Tabellenblatt (Abl.Schleifpuffer)

2. Nach den o.g. Kriterien suchen (Betriebsmittel und Benennung)

3. Erstelle Tabelle mit dem Namen (TabSchleifpuffer)

Mit dem Code so, komm ich nicht zu Rande:

Dim rStart As Range
Set rStart = Sheets("Abl.Schleifpuffer")

Dim i, j As Integer
Dim rStart As Range
Set rStart = Nothing
For i = 1 To 100
For j = 1 To 100
If Cells(i, j).Value = "Betriebsmittel" And Cells(i, j + 1).Value = "Benennung FHMI" Then
Set rStart = Cells(i, j)
Exit For
End If
Next
Next
If rStart Is Nothing Then Debug.Print "Start nicht gefunden": Exit Sub

ActiveSheet.ListObjects.Add(xlSrcRange, Range(rStart, Selection), , xlYes).Name = _
"TabSchleifpuffer"

wahrscheinlich, eher was zum lachen ^^

[Suboptimierer]

@Phila86

Du hast einen Teil deines Codes vergessen:

 rStart.Select
 Range(Selection, Selection.End(xlToRight)).Select
 Range(Selection, Selection.End(xlDown)).Select

[Phila86]

@Suboptimierer

Ich habe echt probieren müssen, aber nun scheint es sehr gut zu funktionieren. Vielen Dank.

Wärst du so nett und schaust nochmal drüber ob da nicht noch ein Fehler drin ist oder mir etwas zum Verhängnis werden kann?

Der Code lautet nun so:

Sub Makro1()
'
' Makro1 Makro
'

'

Sheets("Abl.Schleifpuffer").Select

Dim i, j As Integer
  Dim rStart As Range
  Set rStart = Nothing
  For i = 1 To 100
    For j = 1 To 100
      If Cells(i, j).Value = "Betriebsmittel" And Cells(i, j + 1).Value = "Benennung FHMI" Then
        Set rStart = Cells(i, j)

rStart.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

        Exit For
      End If
    Next
  Next
  If rStart Is Nothing Then Debug.Print "Start nicht gefunden": Exit Sub

ActiveSheet.ListObjects.Add(xlSrcRange, Range(rStart, Selection), , xlYes).Name = _
"TabSchleifpuffer"

End Sub

[Suboptimierer]

@Phila86

Die folgenden drei Zeilen am besten unter "If rStart Is Nothing":

rStart.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select

Sonst musst du halt noch beachten, dass der Anfang immer innerhalb der ersten 100 Zeilen und Spalten sein muss.

Man hätte auch mit Range().Find operieren können, mein Ansatz war aber nicht wesentlich unkomplizierter oder kürzer als der obige.

Für die Zukunft, wenn du fortgeschrittener bist, solltest du dir merken, dass man möglichst wenig mit der Selection arbeiten sollte, da es eine Bremse ist. Am besten immer eigene Range-Objekte verwenden.

Ansonsten sieht das gut aus. (y)