VBA - Intelligente Tab Spalten ansprechen

Forumthread: VBA - Intelligente Tab Spalten ansprechen

11.01.2019 15:55:13

Alexander

Hi zusammen,
bin so verzweifelt das ich mich hier neu angemeldet hab :-)
Ich versuche eine Spalte in einer intelligenten Tabelle (Einfügen Tabelle. In VBA heißen die Dinger glaub ich listobject. wobei ich schon den unterschied zwischen listobject und listobjects nicht verstehe) anzusprechen und aus den Daten in der Spalte einen Mittelwert zu ermitteln.
Diesen Mittelwert möchte ich dann in einem txtfeld Ausgeben.
Die Tabelle heißt: "tabData"
Die Spalte heißt: "Note"
Das Textfeld heißt: "txtNote"
Vielen Dank für Eure hilfe

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Range("tabData[[#Headers],[Note]]")

11.01.2019 18:09:48

Matthias

Hallo
als kleine Denk-Hilfe ...
mit: MsgBox Range("tabData[[#Headers],[Note]]").Offset(1, 0).Address
kannst Du Dir die Adresse anzeigen lassen.

Range("tabData[[#Headers],[Note]]").Offset(2, 0).Address

wäre dann die nächste Zeile der Spalte[Note]
Gruß Matthias

AW: Range("tabData[[#Headers],[Note]]")

15.01.2019 09:48:45

Alexander

Hi Matthias,
ja das funktioniert. Gibt es auch eine Funktion die mir die komplette Spalte addiert oder einen Mittelwert erzeugt?

AW: Range("tabData[[#Headers],[Note]]")

15.01.2019 13:46:37

Alexander

Wie viele Zeilen die Spalte in der Tabelle hat, wäre auch hilfereich...

=MITTELWERT(tabData[[#Alle];[Note]])

17.01.2019 00:26:35

Matthias

Hallo
Kannst Du doch direkt als Formel verwenden:
=MITTELWERT(tabData[[#Alle];[Note]])
Du kannst aber auch den Bereich markieren

Range(Range("tabData[[Note]]").Address).Select

oder sonst was damit machen
einfach den Bereich nur ausgeben:

MsgBox Range("tabData[[Note]]").Address

So kannst Du also auch den Mittelwert per VBA bilden, denn die Range-Adresse hast Du ja nun ;-)
Gruß Matthias

AW: =MITTELWERT(tabData[[#Alle];[Note]])

17.01.2019 10:58:31

Alexander

Weltklasse jetzt hab ich es!
Du bist mein Held des Tages! Danke dir! :)
Wie das mit Helden so ist, folgt nach der Welt Rettung die Nächste :-)
Das gleich möchte ich jetzt auf einer anderen Spalte machen.
Die Spalte heißt Status und es gibt drei Eintragsmöglichkeiten:
- Leer
- Draft
- Final
in Textfeldern möchte ich jetzt ausgeben wie oft welcher Zustand vorkommt.
Natürlich per VBA. Umständlich mit Schleifen und so bekomme ich es bestimmt hin. Gibt es einen coolen einfachen Weg?
Danke Dir

aber auch das geht mit Formeln

17.01.2019 12:02:33

Matthias

Hallo
Die Spalte heißt Status und es gibt drei Eintragsmöglichkeiten:
- Leer
- Draft
- Final
Was bedeutet bei Dir Leer?
Steht das Wort "Leer" in der Zelle oder ist die Zelle leer?
Wenn die Zelle leer ist, kannst Du das mit der Formel:
=ANZAHLLEEREZELLEN(tabData[Status]) ausgeben.
Steht das Wort "Leer" in der Zelle, musst Du
die Gültigkeitsregel um dieses Wort erweitern.
Tabelle1

	D	E	F
6	Datum	Status	Note
7	13.01.2019	Draft	1
8	14.01.2019	Final	2
9	15.01.2019
10	16.01.2019	Draft	1
11	17.01.2019	Final	1
12	17.01.2019	Draft	2

Datengültigkeit der Tabelle

Zelle	Zulassen	Daten	Eingabe 1	Eingabe 2
E7	Liste		Draft;Final
E8	Liste		Draft;Final
E9	Liste		Draft;Final
E10	Liste		Draft;Final
E11	Liste		Draft;Final
E12	Liste		Draft;Final

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Anbei eine Beispieldatei.
https://www.herber.de/bbs/user/126855.xlsm
Gruß Matthias

AW: aber auch das geht mit Formeln

17.01.2019 14:07:16

Alexander

Hi Matthias,
danke für die Datei.
Ohne vba ist es auch voll simple. Excel an sich check ich voll ab auch Formeln, dass ist gar keine Thema.
Mich interessierts es das in VBAP zu machen. :-)
Bis jetzt bin ich soweit:

Private Sub Worksheet_Change(ByVal Target As Range)
'Variablen deklaration
Dim StatusSpalte As Integer
Dim NotenSpalte As Integer
Dim mw As Integer
Dim Tabrange As Variant
Dim WSName As Variant
'Variablen Zuweisung - Spalte in der der Status steht
StatusSpalte = 7
NotenSpalte = 6
WSName = ActiveSheet.Name
'Abfrage ob eine ?nderung sich ergeben hat
If Target.Column = StatusSpalte Then
ThisRow = Target.Row
If Target.Value = "final" Then
Cells(ThisRow, StatusSpalte + 1).Value = Now()
Cells(ThisRow, StatusSpalte + 2).Value = Application.UserName
ElseIf Target.Value = "draft" Then
Cells(ThisRow, StatusSpalte + 1).Value = ""
Cells(ThisRow, StatusSpalte + 2).Value = Application.UserName
Else
Cells(ThisRow, StatusSpalte + 1).Value = ""
Cells(ThisRow, StatusSpalte + 2).Value = ""
End If
End If
'Notenmittelwert errechnen und eintragen
If Target.Column = NotenSpalte Then
Tabrange = Range("tab" & WSName & "[[Note]]").Address
mw = Application.Average(Range(Tabrange))
ActiveSheet.Shapes.Range(Array("txtMarkInt")).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = mw
End If
Range("a2").Select
End Sub

Jetzt fehl mir doch das mit final, draft und leer Zellen. Leer heißt es steht nix drin :-)

AW: aber auch das geht mit Formeln

17.01.2019 14:36:19

Alexander

AnzDraft = WorksheetFunction.CountIf(Range("tab" & WSName & "[[Status VP]]"), "draft")
hab es geschafft :-)
Tabrange = Range("tab" & WSName & "[[Status VP]]").SpecialCells(xlCellTypeBlanks).Count
Die Funktion klappt auch. nur wenn ich keine leeren zellen habe, kommt eine debug fehler.
kann ich den iwie abfangen?

Infobox / Tutorial

Intelligente Tabellen mit VBA: Spalten ansprechen und Daten verarbeiten

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Tabelle erstellen: Erstelle in Excel eine intelligente Tabelle (ListObject) und benenne sie, z.B. "tabData".
Spalten benennen: Stelle sicher, dass deine Spalten Überschriften haben, z.B. "Note" und "Status".
VBA-Editor öffnen: Drücke ALT + F11, um den VBA-Editor zu öffnen.
Modul einfügen: Klicke mit der rechten Maustaste auf deinen Projektordner, wähle "Einfügen" und dann "Modul".

Code einfügen: Füge den folgenden Code ein, um die Spalte "Note" anzusprechen und den Mittelwert zu berechnen:

Sub MittelwertBerechnen()
    Dim mw As Double
    mw = Application.WorksheetFunction.Average(Range("tabData[[Note]]"))
    MsgBox "Der Mittelwert der Noten ist: " & mw
End Sub

Code ausführen: Führe den Code aus, um den Mittelwert der Noten zu berechnen.

Häufige Fehler und Lösungen

Fehler: "table has no column named"
Überprüfe, ob der Name der Spalte korrekt geschrieben ist. Achte auf Groß- und Kleinschreibung.
Fehler beim Ausführen des Codes
Stelle sicher, dass die Tabelle existiert und die Spalten korrekt benannt sind.

Debug-Fehler bei leeren Zellen
Nutze On Error Resume Next, um den Fehler abzufangen:

On Error Resume Next
AnzDraft = WorksheetFunction.CountIf(Range("tabData[[Status]]"), "draft")
On Error GoTo 0

Alternative Methoden

Direkte Verwendung von Formeln: Du kannst den Mittelwert direkt in einer Zelle berechnen:
```
=MITTELWERT(tabData[[#Alle];[Note]])
```
Verwendung von CountIf für Status: Um die Anzahl der Einträge in der Status-Spalte zu zählen, kannst du Folgendes verwenden:
```
Dim AnzDraft As Long
AnzDraft = Application.WorksheetFunction.CountIf(Range("tabData[Status]"), "Draft")
```

Praktische Beispiele

Mittelwert aus einer intelligenten Tabelle berechnen:

Sub MittelwertNote()
    Dim mw As Double
    mw = Application.WorksheetFunction.Average(Range("tabData[[Note]]"))
    ActiveSheet.Shapes("txtNote").TextFrame.Characters.Text = mw
End Sub

Zählen der Werte in der Status-Spalte:

Sub StatusZaehlen()
    Dim AnzDraft As Long
    AnzDraft = Application.WorksheetFunction.CountIf(Range("tabData[Status]"), "Draft")
    MsgBox "Anzahl der Draft-Einträge: " & AnzDraft
End Sub

Tipps für Profis

Nutze die ListObject-Eigenschaften, um auf Daten zuzugreifen. Beispiel: ActiveSheet.ListObjects("tabData").ListColumns("Note").DataBodyRange.
Verwende Range.SpecialCells, um nur die nicht-leeren Zellen zu bearbeiten.
Achte darauf, dass deine VBA-Prozeduren effizient sind, um die Performance beim Arbeiten mit großen Datensätzen nicht zu beeinträchtigen.

FAQ: Häufige Fragen

1. Wie spreche ich eine gesamte Spalte in einer intelligenten Tabelle an?
Verwende Range("tabData[[Note]]") für die gesamte Spalte "Note".

2. Kann ich auch mit leeren Zellen in einer intelligenten Tabelle umgehen?
Ja, du kannst CountIf verwenden und den Fehler mit On Error Resume Next abfangen, um Debug-Fehler zu vermeiden.

Beliebteste Forumthreads (12 Monate)

VBA - Intelligente Tab Spalten ansprechen

Forumthread: VBA - Intelligente Tab Spalten ansprechen

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Intelligente Tabellen mit VBA: Spalten ansprechen und Daten verarbeiten

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

VBA - Intelligente Tab Spalten ansprechen

Forumthread: VBA - Intelligente Tab Spalten ansprechen

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Intelligente Tabellen mit VBA: Spalten ansprechen und Daten verarbeiten

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread