Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
676to680
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
676to680
676to680
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

weitere Spalten

weitere Spalten
06.10.2005 15:47:47
Daniel
Hi...
Ich habe mir überlegt das ich mit ner For Next Schleife doch auch noch andere Spalten prüfen lassen kann. Aber irgendwie haut das net hin. Ich muss aber für jede Spalte andere feste Grenzen festlegen. Mit dem Code (Vielen dank an die Experten von hier) funktioniert das bei einer Spalte. Es soll aber auch Spalte 7,8,11,12,13,14,15,16 durch prüfen. Wie muss ich den Code dann ändern. Für jede Spalte gibt es andere Ober und Untergrenzen.
Vielen Dank Daniel
Private Sub Bereinigung_Click() Const OBERGRENZE As Long = 150000 Const UNTERGRENZE As Long = 0 Const AB_ZEILE As Integer = 6 Const IN_SPALTE As Integer = 7 Dim rng As Range Dim c As Range Dim lngAvg As Long With Worksheets("Daten") Set rng = .Cells(Rows.Count, IN_SPALTE).End(xlUp) If rng.Row > AB_ZEILE Then Set rng = .Range(.Cells(AB_ZEILE, IN_SPALTE), .Cells(Rows.Count, IN_SPALTE).End(xlUp)) For Each c In rng.Cells lngAvg = WorksheetFunction.Average(rng) If c < UNTERGRENZE Or c > OBERGRENZE Then c = lngAvg Next End If Set rng = Nothing End With End Sub

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: weitere Spalten
06.10.2005 16:59:21
Erich
Hallo Daniel,
willst du wirklich, was du programmiert hast? Ich habe da heftige Zweifel. Ein Beispiel:
 
  
 ABH
6111
7-1007-37
8888
9-1007-25
10555
 
 Diagramm - Grafik - Excel Tabellen einfach im Web darstellen    Excel Jeanie HTML  3.0    Download  

In Spalte A ist nur wiedergegeben, was vor dem Start in Spalte H stand. In H12 und H14 wurde der jeweils aktuelle Durchschnitt eingetragen. In H12 fließen noch H12 und H14 mit jeweils -100 ein, in den Durchschnitt für H14 geht H12 mit -37 ein.

Ich vermute, du möchtest in diesen beiden Zellen den Durchschnitt aller "vernünftigen" Werte, also derer, die zwischen Unter- und Obergranze liegen, also die Werte in Spalte B. Dort habe ich (per Hand) den Durchschnitt 7 von 1, 8 und 5 eingetragen.

Hier der Code, der die Spalte H produziert hat:

Private Sub Bereinigung_Click()
Const AB_ZEILE As Integer = 6
Dim rng As Range
Dim c As Range
Dim lngAvg As Long
Dim ii As Integer, SpW(1 To 8, 1 To 3) As Long
ii = ii + 1: SpW(ii, 1) = 7:  SpW(ii, 2) = 0: SpW(ii, 3) = 150000
ii = ii + 1: SpW(ii, 1) = 8:  SpW(ii, 2) = 0: SpW(ii, 3) = 160000
ii = ii + 1: SpW(ii, 1) = 11: SpW(ii, 2) = 0: SpW(ii, 3) = 170000
ii = ii + 1: SpW(ii, 1) = 12: SpW(ii, 2) = 0: SpW(ii, 3) = 180000
ii = ii + 1: SpW(ii, 1) = 13: SpW(ii, 2) = 0: SpW(ii, 3) = 190000
ii = ii + 1: SpW(ii, 1) = 14: SpW(ii, 2) = 0: SpW(ii, 3) = 100000
ii = ii + 1: SpW(ii, 1) = 15: SpW(ii, 2) = 0: SpW(ii, 3) = 110000
ii = ii + 1: SpW(ii, 1) = 16: SpW(ii, 2) = 0: SpW(ii, 3) = 120000
With Worksheets("Daten")
For ii = 1 To 8
If .Cells(Rows.Count, SpW(ii, 1)).End(xlUp).Row > AB_ZEILE Then
Set rng = .Range(.Cells(AB_ZEILE, SpW(ii, 1)), _
.Cells(Rows.Count, SpW(ii, 1)).End(xlUp))
For Each c In rng.Cells
lngAvg = WorksheetFunction.Average(rng)
If c < SpW(ii, 2) Or c > SpW(ii, 3) Then c = lngAvg
Next c
End If
Next ii
Set rng = Nothing
End With
End Sub


Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 19:25:57
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hallo.

Erstmal danke für den code. Er bringt mir aber bei Zeile "lngAvg = WorksheetFunction.Average(rng)" eine Fehlermeldung. Er meint es läge ein Overflow vor. ich weiß leider nicht wie ich das beheben kann.

MfG Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 19:39:33
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

da sehe ich zwei mögliche Ursachen:

1. Die Summe deiner Werte einer Spalte ist zu groß für "Long" - ich würde da Double empfehlen.

2. Der Durchschnitt soll über 0 Werte berechnet werden. 

Gibst du mir noch eine Antwort auf meine allererste Frage?

Und was sagst du zu dem Beispiel, das ich gepostet habe? 

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 19:52:07
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hi.

Deine Erste Frage war ob ich das wirklich will was ich da programmiert habe. Ja das will ich. *g* Dein gepostete Code ist super. 

Kann man den Code so änderen das er nur die Daten zur Durchschnittsberechnung nimmt die in den Grenzen liegen? Das würde ein besseres Ergebnis bringen. Danke

Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:11:26
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

die bisherigen Codes verwendeten für die Durchschnittsbildung auch die "schlechten" Werte. Darauf habe ich dich aufmerksam machen wollen: Die bisherigen Codes rechnen nach meiner Ansicht Unfug aus.

Aus deiner letzten Frage kann ich nur entnehmen, dass du das inzwischen auch nicht mehr willst...

Ich hatte dir geschrieben:

"Ich vermute, du möchtest in diesen beiden Zellen den Durchschnitt aller "vernünftigen" Werte, also derer, die zwischen Unter- und Obergranze liegen,"

Hier der (gewachsene, daher "suboptimale") Code dafür.

(Anmerkung: Der Durchschnitt wird auch in leere oder mit Text gefüllte Zellen eingetragen, die innerhalb des benutzten Bereichs einer Spalte liegen.)



Private Sub Bereinigung_Click()
Const AB_ZEILE As Integer = 6
Const AnzSp = 9
Dim rng As Range
Dim c As Range
Dim lngAvg As Long
Dim ii As Integer, SpW(1 To AnzSp, 1 To 3) As Long
Dim anzOK As Integer, arrOK
ii = ii + 1: SpW(ii, 1) = 6:  SpW(ii, 2) = 0: SpW(ii, 3) = 150000
ii = ii + 1: SpW(ii, 1) = 7:  SpW(ii, 2) = 0: SpW(ii, 3) = 150000
ii = ii + 1: SpW(ii, 1) = 8:  SpW(ii, 2) = 0: SpW(ii, 3) = 160000
ii = ii + 1: SpW(ii, 1) = 11: SpW(ii, 2) = 0: SpW(ii, 3) = 170000
ii = ii + 1: SpW(ii, 1) = 12: SpW(ii, 2) = 0: SpW(ii, 3) = 180000
ii = ii + 1: SpW(ii, 1) = 13: SpW(ii, 2) = 0: SpW(ii, 3) = 190000
ii = ii + 1: SpW(ii, 1) = 14: SpW(ii, 2) = 0: SpW(ii, 3) = 100000
ii = ii + 1: SpW(ii, 1) = 15: SpW(ii, 2) = 0: SpW(ii, 3) = 110000
ii = ii + 1: SpW(ii, 1) = 16: SpW(ii, 2) = 0: SpW(ii, 3) = 120000
With Worksheets("Daten")
For ii = 1 To AnzSp
If .Cells(Rows.Count, SpW(ii, 1)).End(xlUp).Row > AB_ZEILE Then
Set rng = .Range(.Cells(AB_ZEILE, SpW(ii, 1)), _
.Cells(Rows.Count, SpW(ii, 1)).End(xlUp))
'        ------------ "gute" Werte zählen
anzOK = 0
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
anzOK = anzOK + 1
End If
Next c
If anzOK = 0 Then
MsgBox "Kein zulässiger Wert in Spalte" & Str(ii)
Else
'           --------- Array mit "guten" Werten anlegen
ReDim arrOK(1 To anzOK)
anzOK = 0
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
anzOK = anzOK + 1
arrOK(anzOK) = c
End If
Next c
'           --------- Durchschnitt der "guten" Werte berechnen
lngAvg = WorksheetFunction.Average(arrOK)
'           --------- Durchschnitt in die "schlechten" Zellen eintragen
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
Else
c = lngAvg
End If
Next c
End If
End If
Next ii
Set rng = Nothing
End With
End Sub



Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:22:56
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hallo...

Ich habe endlich mit bekommen das es Unfug ist. Bei mir hat es nur etwas gedauert. *lala*

Bei mir bringt er eine Fehlermeldung. Und zwar: Application defined or object defined error. Und zwar nach der Zeile "For ii = 1 To AnzSp". Was bedeutet die Fehlermeldung und wie kann man sie beheben?

Danke Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:31:46
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

bei welcher Spalte (welchem Wert von ii) tritt der Fehler auf?

Welcher Wert steht für dieses ii in SpW(ii, 1) ? Ist der vielleicht Null oder negativ?

Grüße von Erich aus Kamp-Lintfort

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:40:49
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hallo Erich

Wenn ich mit der Maus in die Zeile gehe und dann mir den Wert anzeigen lasse steht immer für ii =  9

Und Spalte 9 soll ja nicht ausgewertet werden da dort nur Text drinne steht...

Daniel

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:48:31
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

ii=9 bedeutet NICHT Spalte 9, sondern die neunte Spalte, die du oben angegeben hast.

In dem von mir geposteten Code steht da 16, also die 16. Spalte.

Welcher Wert steht in SpW(ii, 1)? Ich hatte dich auch danach gefragt...

Grüße von Erich aus Kamp-Lintfort

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 20:53:40
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hi Erich...

Dann könnte es daran liegen. Ich habe das oben auskommentiert, wo Spalte 6 ist. Da dies nicht ausgewertet werden soll. 

Wert für SpW(ii, 1) wird als 0 angezeigt

Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 21:18:37
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

hier eine neue Version. Die verträgt es auch, wenn du weniger als AnzSp Spalten auswerten willst:



Private Sub Bereinigung_Click()
Const AB_ZEILE As Integer = 6
Const AnzSp = 9
Dim rng As Range
Dim c As Range
Dim lngAvg As Integer
Dim ii As Integer, SpW(1 To AnzSp, 1 To 3) As Long
Dim anzOK As Integer, arrOK() As Integer
'  ii = ii + 1: SpW(ii, 1) = 6:  SpW(ii, 2) = 0: SpW(ii, 3) = 150000
ii = ii + 1: SpW(ii, 1) = 7:  SpW(ii, 2) = 0: SpW(ii, 3) = 150000
ii = ii + 1: SpW(ii, 1) = 8:  SpW(ii, 2) = 0: SpW(ii, 3) = 160000
ii = ii + 1: SpW(ii, 1) = 11: SpW(ii, 2) = 0: SpW(ii, 3) = 170000
ii = ii + 1: SpW(ii, 1) = 12: SpW(ii, 2) = 0: SpW(ii, 3) = 180000
ii = ii + 1: SpW(ii, 1) = 13: SpW(ii, 2) = 0: SpW(ii, 3) = 190000
ii = ii + 1: SpW(ii, 1) = 14: SpW(ii, 2) = 0: SpW(ii, 3) = 100000
ii = ii + 1: SpW(ii, 1) = 15: SpW(ii, 2) = 0: SpW(ii, 3) = 110000
ii = ii + 1: SpW(ii, 1) = 16: SpW(ii, 2) = 0: SpW(ii, 3) = 120000
With Worksheets("Daten")
For ii = 1 To AnzSp
'     --------------- "gute" Werte zählen
If SpW(ii, 1) < 1 Or SpW(ii, 1) >= Rows.Count Then GoTo NXT
If .Cells(Rows.Count, SpW(ii, 1)).End(xlUp).Row <= AB_ZEILE Then _
GoTo NXT
Set rng = .Range(.Cells(AB_ZEILE, SpW(ii, 1)), _
.Cells(Rows.Count, SpW(ii, 1)).End(xlUp))
'     ------------ "gute" Werte zählen
anzOK = 0
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
anzOK = anzOK + 1
End If
Next c
If anzOK = 0 Then _
MsgBox "Kein zulässiger Wert in Spalte" & Str(SpW(ii, 1)): GoTo NXT
'     --------- Array mit "guten" Werten anlegen
ReDim arrOK(1 To anzOK)
anzOK = 0
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
anzOK = anzOK + 1
arrOK(anzOK) = c
End If
Next c
'     --------- Durchschnitt der "guten" Werte berechnen
lngAvg = WorksheetFunction.Average(arrOK)
'     --------- Durchschnitt in die "schlechten" Zellen eintragen
For Each c In rng.Cells
If Not IsEmpty(c) And SpW(ii, 2) <= c And c <= SpW(ii, 3) Then
Else
c = lngAvg
End If
Next c
NXT:
Next ii
End With
Set rng = Nothing
End Sub



Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 21:38:04
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Vielen Dank Erich. Es läuft jetzt ohne Probleme. Wo her hast du das ganze Wissen?

Vielen Dank Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            Excel/VBA? Hab ich mir selbst beigebracht...
                                            

                    

                                                    06.10.2005 21:42:43
                                            

                                

                                            Erich
                                    

            

        

        

                            
                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 21:15:31
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Danke Erich, ich habe die Kommentierung wieder rausgenommen und es funktioniert. 

Vielen Dank für deine Hilfe. Dir noch einen schönen Abend

Daniel

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 17:09:45
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

sorry - eine kleine Korrektur zu meinem Beitrag:

Aus den Zeilennummern 12 und 14 sind im Beispiel die Zeilennummern 7 und 9 geworden.  

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 17:32:19
                                            

                                

                                            Daniel
                                    

            

        

        

                            
Hi.

Ich bin aus deiner Ausführung leider nicht schlau geworden. 

Der Code geht eine Spalte durch und prüft ob der Zellenwert in den grenzen liegt. Wenn ja geht er zum nächsten. Wenn nicht berechnet er den Durschnit der Zeilen. 

Und ich möchte jetzt das er, wenn er durch Spalte 6 durch ist soll er Spalte 7 durch gehen. Man muss natürlich die Ober und Untergrenzen von vorn hereinfestlegen.  Ich will mir nur Code sparen. 

Danke Daniel

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


                    
                                
            

    
    

        

            
            

                                    

                                                    
                            AW: weitere Spalten
                                            

                    

                                                    06.10.2005 19:05:01
                                            

                                

                                            Erich
                                    

            

        

        

                            
Hallo Daniel,

der Code, den ich gepostet habe, tut das, was du willst: Er verarbeitet nacheinander die Spalten 7,8,11,12,13,14,15,16 verarbeiten. (Spalte 6 ließe sich leicht auch noch ergänzen.)

Die Unter- und Obergrenzen musst du für jede Spalte einzeln im Code festlegen (in den Zeilen, die mit "ii = ii + 1:" beginnen).

Als Beispiel habe ich dargestellt, was der Code mit Spalte 8 (=H) tut.

Ist dir klar, wie in dem Beispiel die Ergebnisse -37 und -25 entstehen?

(Diese Ergebnisse kommen auch, wenn du die Ausgangswerte in Spalte 6 einträgst und das alte Programm laufen lässt.)

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

                    


                    


    
Anzeige

    

        
                    
            
                
    



            


    
    


            
    
    
    
                    


    
    
    
    
    
    
            


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

    
    
    


    
Anzeige

    

        
                    
                
    




    
    

    

        

            

                
                    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
                
            

            

                

                
            

        

    




    
    

    
    
            
 

            
            

                
                
                                    

                        


    

        
        Beliebteste Forumthreads (12 Monate)
    

    
    

 

                    

                                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige

    

        
                    
            
                
    



                


    
Anzeige