Problem mir VBA-Code

[Volker Neurath]

Hi zusammen,

neues Problem:

Ich wollte gestern die Zellen einer Spalte (ca 12700 Zeilen)
darauf hin überprüfen, ob sie Werte <0 enthalten.
Bei <0 soll eine 0 in die Zelle geschrieben werden.

Mein Ansatz (geklaut bei John Walkenbach):
die Spalte in ein Variant-Array einlesen, das mittels einer FOR...NEXT
Schleife durchlaufen wird. Innerhalb der Schleife erfolgen Test und ggf.
Rückschreiben der 0 in das entsprechende Array-Element.

Wenn fertig, "einfach" das Variant-Array zurück in den Spalten-Range
schreiben.

Habe ich schon unzählige male so gemacht, hat immer funktioniert.

Soweit die Theorie.

Heute wollte Excel jedoch nicht mitspielen.

Hier der Code:

Option Explicit
Option Base 1

Sub AlleNegativenNull()

Dim TR As Variant

Dim i As Long


TR = Range("B1:B84").Value


'die Debug.Print Zeilen sind noch von meinen Versuchen
'einer Fehlersuche übrig

'Erstmal nur 84 Zeilen

For i = 1 To 84
Debug.Print i
Debug.Print TR(i)
If TR(i) < 0 Then
TR(i) = 0
End If

Next i

Range("B1:B84").Value = TR

End Sub

Schon an der zweiten Debug.Print bricht die Ausführung des Codes mit
der Meldung ab, ein Parameter sei außerhalb des gültigen Wertebereiches
- damit kann ja eigentlich nur die Laufvariable i gemeint sein, so dass
TR(i) scheitert. Aber: warum?

Heute habe ich es mit einer anderen Codevariante nochmal getestet:

Sub AlleNegativenNull()

Dim TR As Variant

Dim i As Long


TR = Range("A1:B84").Value


'die Debug.Print Zeilen sind noch von meinen Versuchen
'einer Fehlersuche übrig

'Erstmal nur 84 Zeilen

For i = 1 To UBound(TR,1)

If TR(i,2) < 0 Then
TR(i,2) = 0
End If

Next i

Range("B1:B84").Value = TR

End Sub

Und oh wunder: geht.

kann mir bitte mal jemand auf die Sprünge helfen und erklären, warum
der zweite Code funktioniert, der erste nicht?

[Carlos Naplos]

Hi

Range("B1:B84").Value liefert ein zweidimensionales Array
Variant(1 to 84, 1 to 1)

D.h. deine erste Variante funktioniert, wenn du TR(i,1) statt TR(i)
schreibst.

Die Logik ist wohl, dass du 84 Zeilen und eine Spalte hast.

Bei Range("A1:B84").Value hast du Variant(1 to 84, 1 to 2) und mit
TR(i,2) die i-te Zeile der zweite Spalte beschrieben.

Das siehst du z.B., wenn du für Range("B1:B84").Value eine Überwachung
hinzufügst oder bei TR im Lokalfenster des VBA-Editors.

Eine andere Frage ist, was dir das Array bringt.

Option Explicit

Sub AlleNegativenNull()

Dim rgZelle As Range

For Each rgZelle In Range("B1:B84")
If rgZelle < 0 Then rgZelle = 0
Next rgZelle

End Sub


Gruß CN

Post by Volker Neurath
Hi zusammen,
Ich wollte gestern die Zellen einer Spalte (ca 12700 Zeilen)
darauf hin überprüfen, ob sie Werte <0 enthalten.
Bei <0 soll eine 0 in die Zelle geschrieben werden.
die Spalte in ein Variant-Array einlesen, das mittels einer FOR...NEXT
Schleife durchlaufen wird. Innerhalb der Schleife erfolgen Test und ggf.
Rückschreiben der 0 in das entsprechende Array-Element.
Wenn fertig, "einfach" das Variant-Array zurück in den Spalten-Range
schreiben.
Habe ich schon unzählige male so gemacht, hat immer funktioniert.
Soweit die Theorie.
Heute wollte Excel jedoch nicht mitspielen.
Option Explicit
Option Base 1
Sub AlleNegativenNull()
Dim TR As Variant
Dim i As Long
TR = Range("B1:B84").Value
'die Debug.Print Zeilen sind noch von meinen Versuchen
'einer Fehlersuche übrig
'Erstmal nur 84 Zeilen
For i = 1 To 84
Debug.Print i
Debug.Print TR(i)
If TR(i) < 0 Then
TR(i) = 0
End If
Next i
Range("B1:B84").Value = TR
End Sub
Schon an der zweiten Debug.Print bricht die Ausführung des Codes mit
der Meldung ab, ein Parameter sei außerhalb des gültigen Wertebereiches
- damit kann ja eigentlich nur die Laufvariable i gemeint sein, so dass
TR(i) scheitert. Aber: warum?
Sub AlleNegativenNull()
Dim TR As Variant
Dim i As Long
TR = Range("A1:B84").Value
'die Debug.Print Zeilen sind noch von meinen Versuchen
'einer Fehlersuche übrig
'Erstmal nur 84 Zeilen
For i = 1 To UBound(TR,1)
If TR(i,2) < 0 Then
TR(i,2) = 0
End If
Next i
Range("B1:B84").Value = TR
End Sub
Und oh wunder: geht.
kann mir bitte mal jemand auf die Sprünge helfen und erklären, warum
der zweite Code funktioniert, der erste nicht?

[Volker Neurath]

Am Sat, 8 Oct 2022 03:09:43 +0200

Post by Carlos Naplos
Hi
Range("B1:B84").Value liefert ein zweidimensionales Array
Variant(1 to 84, 1 to 1)

Danke für die Bestätigung meines Verdachtes, den ich gestern nach dem
erfolgreichen Test hatte.

Post by Carlos Naplos
Die Logik ist wohl, dass du 84 Zeilen und eine Spalte hast.

Korrekt. Die vollständige Datei hat sogar 12716 Zeilen und zwei
Spalten; es handelt sich dabei um einen Export aus unserer WaWi: die
Artikelnummern (Spalte 1) und den Lagerbestand (Spalte 2) aller unserer
Artikel.

Post by Carlos Naplos
Eine andere Frage ist, was dir das Array bringt.

siehe unten unter dem Code

Post by Carlos Naplos
Option Explicit
Sub AlleNegativenNull()
Dim rgZelle As Range
For Each rgZelle In Range("B1:B84")
If rgZelle < 0 Then rgZelle = 0
Next rgZelle
End Sub

Diese FOR-Schleife, mit der die zellen direkt durchlaufen und
manipuliert werden braucht länger, als Einlesen in ein Array,
durchlaufen desselben und Rückschreiben des Arrays in die Range.

Habe ich schon vor ein paar Jahren mehrfach getestet, weil ich es
selbst kaum glauben konnte, aber John Walkenbach hat hier recht.

** Fullquote entfernt ***