Hallo,
hier erst mal ein paar Anmerkungen zu deinem Code, und die geraten,
so leid es mir tut, über weite Strecken zur Fundamentalkritik. Erst mal
kannst du echt froh sein, dass dein Code einen Fehler produziert hat,
sonst würde er jetzt noch laufen ;-) und zwar wegen der mit Verlaub
unsinnigen Schleifenbedingung. Du weist der Variablen lngZeile die
Anzahl der Zeilen im benutzten Bereich zu - weil deine Spalten außer
der ersten bei 1 beginnen und außer der letzten bei 65536 enden, ist
das also immer 65536. Dieser Wert ändert sich im gesamten Code nicht
mehr und ist also schon von daher völlig ungeeignet als Abbruchbedingung.
Außerdem schreibst du Do Until lngZeile = True. Diese Bedingung wäre
nur dann erfüllt, wenn die Variable irgendwann den Wert -1 annehmen
würde, denn das ist der numerische Wert von True. Soviel zum ersten.
Fehler 1004 besagt, dass ein Befehl mit den Konventionen der Anwendung
- hier also Excel - kollidiert, z. B. wenn eine Variable den Wert 0 annimmt
und damit eine Zeile oder Spalte adressiert wird. Es wäre hilfreich gewesen,
wenn du geschrieben hättest, welcher Befehl den Fehler verursacht hat,
dann müsste ich jetzt nicht darüber nachdenken. Allerdings ist das hier
offensichtlich: Der Fehler tritt in dem Moment auf, wo die Variable j einen
Wert über 65536 annimmt, und das ist bei dem 100er-Block ab Zeile 65504
der Fall. Ganz konkret wird der Fehler also durch Cells(j, 1) verursacht,
weil hier j = 65603 ist, aber nur Werte bis 65536 annehmen darf. Du
überwachst nämlich nicht diejenigen Variablen (i, j), mit denen du die
Zeilen adressierst, sondern die Variable lngZeile, und der weist du ja am
Anfang nur einmalig die Anzahl der Zeilen im Bereich zu. Dasselbe trifft
übrigens für die Spalten zu: Du erhöhst zwar in dem Moment, wo lngZeile
den Wert 65537 annnimmt (also nie...) die Variable intSpalte um 1, aber
deine Range adressierst du immer mit Cells(i, 1). Wenn der Fehler nicht
aufträte, würdest du also immer wieder die 1. Spalte abarbeiten.
Eine letzte Bemerkung: In deinem With-Block nimmst du nur einmal Bezug
auf die With-Variable Tabelle1, nämlich bei UsedRange. Ansonsten
schreibst du immer Range bzw. Cells ohne Punkt. Wenn dein Workbook
mehrere Sheets hätte und ein anderes Blatt als das, das du bearbeiten
willst, aktiv wäre, würden sich diese Befehle dann immer auf dieses
aktive Blatt beziehen! Und noch eine Frage: Ist definitiv sicher, dass in
der Spalte 5, in die du deine Mittelwerte schreibst, keine auszuwertenden
Daten mehr stehen?
Und so könnte eine mögliche Lösung aussehen:
Private Sub Mittelwert()
Dim dz As Long, ds As Integer ' Datenzeile, -spalte
Dim ez As Long ' Endzeile des aktuellen 100er-Blocks
Dim mz As Long, ms As Integer ' Zeile/Spalte für Mittelwerte
Dim ls As Integer, lz As Long ' letzte Spalte, letzt Zeile in ls
Dim rv As Range ' aktueller 100er-Block
' Ende des Datenbereiches ermitteln
ls = Range("IV1").End(xlToLeft).Column
lz = Cells(65536, ls).End(xlUp).Row
' Anfangswerte
dz = 4
ds = 1
mz = 1
ms = ls + 2 ' eine Spalte freilassen
Columns(ms).NumberFormat = "0.000" ' 3 Nachkommastellen
' ersten Block vor Schleifenbeginn festlegen
Set rv = Range(Cells(dz, dz), Cells(dz + 99, ds))
Do
Cells(mz, ms) = Application.WorksheetFunction.Average(rv)
dz = dz + 100
ez = dz + 99
If ez > 65536 Then ' 100er-Block überschreitet Ende der Spalte
Set rv = Range(Cells(dz, ds), Cells(65536, ds))
' Teilbereich bis Ende der Spalte
ds = ds + 1
ez = 99 - (65536 - dz)
dz = 1
Set rv = Union(rv, Range(Cells(dz, ds), Cells(ez, ds)))
' Anfang der nächsten Spalte hinzufügen
Else
Set rv = Range(Cells(dz, ds), Cells(ez, ds))
End If
' Zelladresse für nächsten Mittelwert
mz = mz + 1
If mz = 65537 Then
mz = 1
ms = ms + 1
End If
Loop Until ds = ls And dz > lz
' nächster 100er-Block liegt außerhalb des Datenbereiches
End Sub
Weil nach deinen Worten nicht anzunehmen ist, dass jemals die letzte
Spalte des Blattes erreicht wird, habe ich diesbezüglich keine Kontrolle
eingebaut. Sollte dies dennoch geschehen, tritt ein Fehler auf.
Gruß
|