Average über mehrere Worksheets

Hallo zusammen,
ich versuche gerade ein Makro zu schreiben, welches mir den Mittelwert von Zellen über mehrere Worksheets berechnet.
Die Anzahl der Worksheets über die der Mittelwert gebildet wird, ist mir vorher jedoch nicht bekannt, kann aber natürlich im Makro ermittelt werden. Vor allem weiß ich aber wie das erste und letzte Worksheet heißen: ws1 und wsX
Was bisher funktioniert ist das:

wsAverage.Cells(1, 1) = Application.Average(Sheets(ws1).Cells(1, 1), Sheets(ws2).Cells(1, 1))

Das berechnet den Durchschnitt von zwei Sheets.
Meine Idee war jetzt, einen den Mittelwert über einen Range zu nehmen, in etwa so:

wsAverage.Cells(1, 1) = Application.Average(range(Sheets(ws1).Cells(1, 1), Sheets(wsX).Cells(1, 1)))

Wobei wsX wiegesagt das letzte Worksheet ist über das der Mittelwert gebildet wird.
Dabie bekomme ich jedoch den Fehler: "Method 'Range' of Object '_Global' failed"
Weiß jemand warum das nicht funktioniert?

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Sub t() Dim i As Integer ReDim ar(0) As Variant For i = 1 To ThisWorkbook.Worksheets.Count With Worksheets(i) If .Name = "Ende" Then Exit For Else If .Range("A1") "" Then ReDim Preserve ar(UBound(ar) + 1) ar(UBound(ar)) = .Range("A1") End If End If End With Next i MsgBox WorksheetFunction.Average(ar) End Sub

Hi
in VBA ist es nicht möglich, einen Zellbereich über mehrere Tabellenblätter hinweg zu definieren.
Eine Range ist immer fest einem Worksheet zugeordent, daher müssen die Cells, die die Range definieren, auch immer auf einem Sheet liegen.
ein Zellbereich, der mehrere Tabellenblätter umfasst, ist so nur als in einer Excelzelle möglich und das auch nur für einige Funktionen (Summe, Mittelwert)
in VBA gibt es diese Möglichkeit nicht, hier müsste man sich mit einem Workaround behelfen.
hierfür gibt es mehrere Möglichkeiten:
a) wie von Chris beschrieben, in einer Schleife über die die Tabellenblätter laufen, die Werte sammeln und den Mittelwert berechnen.
b) die Formel in eine freie Excelzelle schreiben (Excel hat genügend davon) und dann den Formelwert abfragen:

Cells(3, 1).Formula = "=Average('" & ws1.Name & ":" & wsX.Name & "'!A1)"
Ergebnis = Cells(3, 1).Value

c) das Formelergebnis mit EVALUATE ermitteln:

Ergebnis = Evaluate("=average('" & ws1.Name & ":" & wsX.name & "'!A1)"

Gruß Daniel

Beliebteste Forumthreads (12 Monate)

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread