[VBA] Schleifen über Zeilen
Holger Schulz
ich m�chte eine Funktion schreiben, die zwei Zeilenst�cke als Parameter
�bergeben bekommt, z.B. A1:J1 und A5:J5.
Jetzt m�chte ich eine Schleife �ber eine der Zeilen, sagen wir die
untere, laufen lassen. Wenn dann z.B. f�r den Wert von C5 eine bestimmte
Bedingung erf�llt ist, m�chte ich mit der entsprechenden Zelle der
oberen Zeile, als C1, etwas berechnen.
�bergebe ich jetzt die beiden Zeilen als ParamArray h�ngen die beide in
einem Range zusammen und ich wei� nicht genau, wie ich die in sagen wir
zwei Listen oder �hnliches zerlegen kann.
Ich hoffe meine Ausf�hrungen sind halbwegs verst�ndlich und mir kann
jemand einen Tipp geben. Daf�r w�re ich dankbar.
hs
Andreas Killer
> ich m�chte eine Funktion schreiben, die zwei Zeilenst�cke als Parameter
> �bergeben bekommt, z.B. A1:J1 und A5:J5.
>
> Jetzt m�chte ich eine Schleife �ber eine der Zeilen, sagen wir die
> untere, laufen lassen. Wenn dann z.B. f�r den Wert von C5 eine bestimmte
> Bedingung erf�llt ist, m�chte ich mit der entsprechenden Zelle der
> oberen Zeile, als C1, etwas berechnen.
Mit einer UDF wird das nicht gehen, weil eine UDF �bergebene Bereiche
nicht modifizieren darf.
> �bergebe ich jetzt die beiden Zeilen als ParamArray h�ngen die beide in
> einem Range zusammen und ich wei� nicht genau, wie ich die in sagen wir
> zwei Listen oder �hnliches zerlegen kann.
Hier kann ich Dir nicht folgen.
> Ich hoffe meine Ausf�hrungen sind halbwegs verst�ndlich und mir kann
> jemand einen Tipp geben. Daf�r w�re ich dankbar.
Ist recht einfach, man kann ein Range genau wie ein Array ansprechen,
wobei die obere linke Zelle des Range immer die Indices 1,1 hat:
Sub Test()
Dim Bereich1 As Range, I As Long
Set Bereich1 = Range("A1:J1")
Set Bereich2 = Range("A5:J5")
For I = 1 To Bereich1.Columns.Count
If Bereich2(1, I) = 3 Then Bereich1(1, I) = "Hier"
Next
End Sub
Andreas.
Holger Schulz
> Holger Schulz schrieb:
>
> > ich m�chte eine Funktion schreiben, die zwei Zeilenst�cke als Parameter
> > �bergeben bekommt, z.B. A1:J1 und A5:J5.
> >
> > Jetzt m�chte ich eine Schleife �ber eine der Zeilen, sagen wir die
> > untere, laufen lassen. Wenn dann z.B. f�r den Wert von C5 eine bestimmte
> > Bedingung erf�llt ist, m�chte ich mit der entsprechenden Zelle der
> > oberen Zeile, als C1, etwas berechnen.
> Mit einer UDF wird das nicht gehen, weil eine UDF �bergebene Bereiche
> nicht modifizieren darf.
Nur nebenbei: Was ist UDF? Modifizieren m�chte ich den �bergebenen
Bereich auch nicht, ich m�chte die darin enthalteten Werte lediglich als
Eingaben verwenden.
>
> > �bergebe ich jetzt die beiden Zeilen als ParamArray h�ngen die beide in
> > einem Range zusammen und ich wei� nicht genau, wie ich die in sagen wir
> > zwei Listen oder �hnliches zerlegen kann.
> Hier kann ich Dir nicht folgen.
Der �bergebene Bereich besteht aus zwei Zeilen, ich h�tte gerne jede
dieser beiden Zeilen in einem eigenen Array.
>
> > Ich hoffe meine Ausf�hrungen sind halbwegs verst�ndlich und mir kann
> > jemand einen Tipp geben. Daf�r w�re ich dankbar.
> Ist recht einfach, man kann ein Range genau wie ein Array ansprechen,
> wobei die obere linke Zelle des Range immer die Indices 1,1 hat:
>
> Sub Test()
> Dim Bereich1 As Range, I As Long
> Set Bereich1 = Range("A1:J1")
> Set Bereich2 = Range("A5:J5")
>
> For I = 1 To Bereich1.Columns.Count
> If Bereich2(1, I) = 3 Then Bereich1(1, I) = "Hier"
> Next
> End Sub
Dabke f�r das Beispiel. Es hilft mir nur bedingt. Erstens leigen hier
bereits zwei Bereiche vor und zeitens werden diese nicht als Parameter
an die Funktion �bergeben.
Ich m�sste wissen, wie ich von einem �bergebenem ParamArray z.B. Zeilen-
und Spaltenzahl bestimmen kann.
Danke.
hs
>
> Andreas.
Peter Schleif
>
> ï¿œbergebe ich jetzt die beiden Zeilen als ParamArray hï¿œngen die beide in
> einem Range zusammen und ich weiￜ nicht genau, wie ich die in sagen wir
> zwei Listen oder ï¿œhnliches zerlegen kann.
Vielleicht suchst Du ja die Eigenschaft .Areas die jeder Bereich in VBA
hat: Eine 1-basierte Collection der Teil-Bereiche. Bei nicht
zusammenhï¿œngenden Bereichen ist Areas.Count >1. Zugriff auf die
Teil-Bereiche bekommst Du mit Areas(1), Areas(2), usw. Das sind dann
wieder normale Range-Objekte, die Du mit einer Schleife durchlaufen
kannst. Mir ist nicht klar, was Du machen willst. Daher steht unten mal
ein allgemeines Beispiel.
Peter
Sub test()
ZweiBereiche Union([A1:J1], [A5:J5])
End Sub
Sub ZweiBereiche(r As Range)
Dim s As Integer
If r.Areas.Count < 2 Then
MsgBox "zu wenige Teil-Bereiche"
Exit Sub
End If
For s = 1 To r.Areas(2).Columns.Count
If s > r.Areas(1).Columns.Count Then
MsgBox "Ende von Bereich 1 erreicht"
Exit For
End If
If r.Areas(2)(1,s) = "x" Then
r.Areas(1)(1,s).Interior.ColorIndex = 3
End If
Next
End Sub
Thomas Ramel
Holger Schulz schrieb am 25.12.2009
> ich m嚙箱hte eine Funktion schreiben, die zwei Zeilenst嚙箱ke als Parameter
> 嚙箭ergeben bekommt, z.B. A1:J1 und A5:J5.
>
> Jetzt m嚙箱hte ich eine Schleife 嚙箭er eine der Zeilen, sagen wir die
> untere, laufen lassen. Wenn dann z.B. f嚙緝 den Wert von C5 eine bestimmte
> Bedingung erf嚙締lt ist, m嚙箱hte ich mit der entsprechenden Zelle der
> oberen Zeile, als C1, etwas berechnen.
>
> 嚙箭ergebe ich jetzt die beiden Zeilen als ParamArray h嚙緯gen die beide in
> einem Range zusammen und ich wei嚙�nicht genau, wie ich die in sagen wir
> zwei Listen oder 嚙篁nliches zerlegen kann.
Warum 嚙箭ergibst Du die beiden Bereiche nicht getrennt?
Die folgenden Zeilen berechnen die Summe der Werte im ersten Bereich wenn
dieselbe Zelle im zweiten Bereich >1 ist:
Public Function tr(Bereich1 As Range, Bereich2 As Range)
Dim lngZelle As Long
Dim dblSum As Double
For lngZelle = 1 To Bereich2.Cells.Count
If Bereich2(lngZelle).Value > 1 Then
dblSum = dblSum + Bereich1(lngZelle).Value
End If
Next lngZelle
tr = dblSum
End Function
Mit freundlichen Gr嚙編sen
Thomas Ramel
--
- MVP f嚙緝 Microsoft-Excel -
[Vista Ultimate SP-1 / xl2007 SP-1]
Holger Schulz
> Gr�ezi Holger
>
> Holger Schulz schrieb am 25.12.2009
>
> > ich m�chte eine Funktion schreiben, die zwei Zeilenst�cke als Parameter
> > �bergeben bekommt, z.B. A1:J1 und A5:J5.
> >
> > Jetzt m�chte ich eine Schleife �ber eine der Zeilen, sagen wir die
> > untere, laufen lassen. Wenn dann z.B. f�r den Wert von C5 eine bestimmte
> > Bedingung erf�llt ist, m�chte ich mit der entsprechenden Zelle der
> > oberen Zeile, als C1, etwas berechnen.
> >
> > �bergebe ich jetzt die beiden Zeilen als ParamArray h�ngen die beide in
> > einem Range zusammen und ich wei� nicht genau, wie ich die in sagen wir
> > zwei Listen oder �hnliches zerlegen kann.
>
> Warum �bergibst Du die beiden Bereiche nicht getrennt?
Das habe ich mich dann auch gefragt. Wom�glich, weil ich Ranges und
ParamArrays durcheinander geworfen habe.
>
> Die folgenden Zeilen berechnen die Summe der Werte im ersten Bereich wenn
> dieselbe Zelle im zweiten Bereich >1 ist:
Das ist so ziemlich das, was ich suche. Wenn man erst mal wei�, was man
sucht, findet's sich nochmal so leicht.
Vielen Dank, auch an Peter und Andreas.
hs
Bernd P
Vielleicht helfen am besten einige UDF (user defined functions =
benutzerdefinierte Funktionen) Beispiele:
http://sulprobil.com/html/listfreq.html
Ansonsten wären für uns ein oder zwei genaue Rechenbeispiele (was Du
erreichen möchtest) gut.
Dass Du keine Eingabebereiche überschreiben willst, halte ich für
sinnvoll (sonst entsteht leicht ein Rekursionsproblem: was soll
geschehen, wenn die Berechnung mehrfach durchgeführt wird?).
Viele Grüße,
Bernd
Andreas Killer
>> Mit einer UDF wird das nicht gehen, weil eine UDF �bergebene Bereiche
>> nicht modifizieren darf.
> Nur nebenbei: Was ist UDF? Modifizieren m�chte ich den �bergebenen
Entschuldigung, UDF ist "Excel-Slang": User Defined Function
Also eine benutzerdefinierte Funktion. In der Regel wird so ein Makro,
welches den Prozedurtyp Function haben muss, bezeichnet das in einem
Tabellenblatt aufgerufen werden kann.
> Bereich auch nicht, ich m�chte die darin enthalteten Werte lediglich als
> Eingaben verwenden.
Dann ist das Okay wenn Du das Ergebnis �ber die UDF zur�ckgibst.
>>> �bergebe ich jetzt die beiden Zeilen als ParamArray h�ngen die beide in
>>> einem Range zusammen und ich wei� nicht genau, wie ich die in sagen wir
>>> zwei Listen oder �hnliches zerlegen kann.
>> Hier kann ich Dir nicht folgen.
> Der �bergebene Bereich besteht aus zwei Zeilen, ich h�tte gerne jede
> dieser beiden Zeilen in einem eigenen Array.
Wenn Du nur 2 Zeilen �bergeben m�chtest, dann ist es sinnvoller diese
zwangsweise abzufordern:
function MyTest(Bereich1 as Range, Bereich2 as Range) as Variant
dim I as Long
For I = 1 To Bereich1.Columns.Count
If Bereich1(1, I) = 3 Then
MyTest = Bereich2(1, I)
Exit Function
End If
Next
end function
Somit wirst Du bei der Eingabe der Formel im Tabellenblatt
=MyTest(A1:J1;A5:J5)
zur �bergabe der beiden Bereiche gezwungen.
Wenn Du die Bereiche �ber ein ParamArray �bergibst geht das zwar auch
Function MyTest2(ParamArray Bereich()) As Variant
Dim I As Long
For I = 1 To Bereich(1).Columns.Count
If Bereich(1)(1, I) = 3 Then
MyTest2 = Bereich(2)(1, I)
Exit Function
End If
Next
End Function
jedoch ist das schon bei der Eingabe irref�hrend, weil dann Excel auch
A10 =MyTest2() '#WERT!
A10 =MyTest2(A1:J1;A2:J2;A3:J3;A4:J4) '0
als g�ltige Eingabe akzeptiert.
Und in der UDF m�sstest Du pr�fen ob �berhaupt und wieviele Parameter
�bergeben wurden und zus�tzlich ob das �berhaupt Bereiche
(Zelle/Range) sind, da ParamArray leider keine Typisierung ala
Function MyTest(ParamArray Bereich() as Range ) As Variant
erlaubt.
> Ich m�sste wissen, wie ich von einem �bergebenem ParamArray z.B. Zeilen-
> und Spaltenzahl bestimmen kann.
Das hat nichts mit einem ParamArray zu tun, ein ParamArray erlaubt
einfach gesagt die �bergabe einer beliebigen Anzahl von Argumenten.
Wenn Du erzwingen m�chtest das Du einen Zellbereich �bergeben
bekommst, dann definiere das Argument "as Range". Nun kann man zwar
trotzdem im Tabellenblatt =MyTest(1;2) eingeben, bekommt aber sofort
den Fehler #WERT! zur�ck und der Code der UDF wird nicht ausgef�hrt,
da Excel selber erkennt die Argumente keine Bereiche sind.
Wenn nun Bereiche �bergeben wurden, dann kann man diesen sehr einfach
pr�fen, weil
a.) man immer einen 2dimensionalen Bereich (Array) bekommt
b.) dieser immer bei Bereich(1,1) anf�ngt
c.) immer bis Bereich(Bereich.Rows.Count, Bereich.Columns.Count) geht
Anzahl Zeilen = Bereich.Rows.Count, Spalten = Bereich.Columns.Count
Jetzt bleibt eigentlich nur noch die Frage was macht man die beiden
Bereiche gepr�ft und festgestellt hat das sie unterschiedlich gro�
sind: Einen Fehler mit CVErr() zur�ckgeben.
Excel hat hier ein paar vordefinierte Konstanten f�r Fehlerwerte:
'xlErrDiv0 = "#DIV/0!"
'xlErrNA = "#NV"
'xlErrName = "#NAME?"
'xlErrNull = "#NULL!"
'xlErrNum = "#ZAHL!"
'xlErrRef = "#BEZUG!"
'xlErrValue = "#WERT!"
Function MyTest(Bereich1 As Range, Bereich2 As Range) As Variant
Dim I As Long, J As Long
If Bereich1.Rows.Count <> Bereich2.Rows.Count Or _
Bereich1.Columns.Count <> Bereich2.Columns.Count Then
MyTest = CVErr(xlErrRef)
Exit Function
End If
For J = 1 To Bereich1.Rows.Count
For I = 1 To Bereich1.Columns.Count
If Bereich1(J, I) = 3 Then
MyTest = Bereich2(J, I)
Exit Function
End If
Next
Next
End Function
Ich denke nun sollte einiges klarer sein. Noch Fragen?
Andreas.