avalon1
Sep 24, 2010, updated at 05:00:55 (UTC)
view16641
view13
0
Goto Top

VBA - Worksheet (Spalte) durchsuchen und Werte auslesen

GermansolvedQuestionVB for ApplicationsDevelopment
Hello

ich habe wieder mal ein kleines VBA Problem,
ich sitzt schon seit ein paar Tagen dran und bin
schon auf ein paar Wege draufgekommen
aber jetzt hänge ich total und hoffe es hat jemand einen Einfall

Ich habe 2 Sheets

das 1. ist das "Data"-Sheet
hier sind die Daten wie folgt eingetragen:
Spalte A Spalte B Spalte C
1.8.10 LKW1 450
1.8.10 LKW1 500
1.8.10 LKW2 370
1.8.10 LKW2 270
1.8.10 LKW3 ....

im 2. Sheet "Ergebnis"Sheet sollen diese Daten zusammengefasst werden
das sollte dann in etwa so aussehen
Spalte A Spalte B Spalte C
1.8.10 LKW1 950
1.8.10 LKW2 640
1.8.10 LKW3 ....

Zur Info:
Im 2. Sheet stehen die Daten (wie Datum und LKWs) schon drinnen
ich muss hier nur das Gewicht eintragen (Summe)


Private Sub DatenSammeln()

    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A1") = "Datum"  
    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("B1") = "LKW"  
    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("C1") = "Gewicht"  
    
    Dim Datum As Date
    Dim TempDatum As Date
    
    Dim LKW As String
    Dim TempLKW As String
    
    Dim Gewicht As Long
    Dim TempGewicht As Long
    
    Dim Zaehler As Long
    Zaehler = 2
    Dim Zaehler2 As Long
    Zaehler2 = 2
    
    While Not IsEmpty(Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A" + Trim(Str(Zaehler))))  
    
        Datum = Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A" + Trim(Str(Zaehler)))  
        LKW = Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("B" + Trim(Str(Zaehler)))  
                
'      DAS IST DIE SCHLEIFE DIE ICH BIS DATO HABE      
'        While Datum = Workbooks("Auswertung.xls").Worksheets("Data").Range("A" + Trim(Str(Zaehler2)))  
'               TempLKW = Workbooks("Auswertung.xls").Worksheets("Data").Range("B" + Trim(Str(Zaehler2)))  
'               If LKW = TempLKW Then  
'                           TempGewicht = Workbooks("Auswertung.xls").Worksheets("Data").Range("C" + Trim(Str(Zaehler2)))  
'                          Gewicht = Gewicht + TempGewicht  
'               End If  
'               Zaehler2 = Zaehler2 + 1  
'        Wend  
'  
'        Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("C" + Trim(Str(Zaehler))) = Gewicht  
'        Gewicht = 0  
        
        Zaehler2 = 2
        
        Zaehler = Zaehler + 1
        
    Wend
    
End Sub

Ich hole mir hier die Daten Datum und LKW, von Sheet2,
(MEIN PROBLEM) muss dann ins "Data"Sheet die geholten Werte
mit den dort vorhandenen vergleichen und wenn beide ident sind dann
das Gewicht holen und ins "Ergebnis" eintragen
funktioniert soweit gut
aber nur beim 1. d. Monats, ab dem zweiten hab ich ein Problem

vielleicht kennt wer eine leichtere Lösung dieses Problem zu händeln

ich weiß es gibt einen Formel (SVerweis...glaub ich) damit kann
man sagen wenn in SpalteA das steht und in SpalteB das, dann hol
Wert aus SpalteC und speicher in zb. in Variable dann mach summe

oder so in etwa face-smile

lg
ava
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 151668

Url: https://administrator.de/contentid/151668

Printed on: April 26, 2024 at 04:04 o'clock

13 Comments
Latest comment
Goto Top
Member: bastla
bastla Sep 24, 2010 at 06:16:18 (UTC)
Goto Top
Hallo Avalon1!

Sollte sich als "Array"-Formel (am Beispiel der Zeile 2) so lösen lassen:
=SUMME((Data!$A:$A=Ergebnis!A2)*(Data!$B:$B=Ergebnis!B2)*Data!$C:$C)
Wichtig: Bei der Ersteingabe und nach jeder Bearbeitung ist die Formel mit Strg + Umsch + Enter zu bestätigen (und wird danach in der Bearbeitungsleiste in geschwungenen Klammern dargestellt)!

Grüße
bastla
Member: Avalon1
Avalon1 Sep 24, 2010 at 08:09:00 (UTC)
Goto Top
Super bastla, danke Dir !!

hab zwar schon geschaut wie ich formeln in VBA einbinden, bis dato hab ich es
aber noch nicht ganz verstanden...werds mal mit deinem "input" testen, danke dir!!!!

lg
ava
Mitglied: 76109
76109 Sep 25, 2010 at 10:31:48 (UTC)
Goto Top
Hallo bastla!

Hast Du die Array-Formel mal getestet und funktioniert's bei Dir?

Gruß Dieter
Member: bastla
bastla Sep 25, 2010 at 11:49:56 (UTC)
Goto Top
Hallo Dieter!

Ja und ja; ist aber schon etwas zäh, wenn die gesamten Spalten (insbes ab Office 2007) berücksichtigt werden - mit einer Einschränkung der Art
=SUMME((Data!$A2:$A1000=Ergebnis!A2)*(Data!$B2:$B1000=Ergebnis!B2)*Data!$C2:$C1000)
geht's natürlich schneller ...

Grüße
bastla
Mitglied: 76109
76109 Sep 25, 2010 at 12:06:36 (UTC)
Goto Top
Hallo bastla!

Aaaaah danke, jetzt funktionierts bei mir auchface-smile Bei der anderen Formel kam bei mir immer #Zahl und muss wohl an der Überschrift gelegen haben?

Gruß Dieter

PS. Schon ne tolle Sache mit den Array-Formelnface-wink
Member: bastla
bastla Sep 25, 2010 at 12:15:26 (UTC)
Goto Top
Hallo Dieter!
muss wohl an der Überschrift gelegen haben?
Konkret: der Spalte C (die hatte ich tatsächlich beim Testen nicht eingegeben face-sad) ...

Abfangen ließe sich das zwar so:
=SUMME((Data!$A:$A=Ergebn!A2)*(Data!$B:$B=Ergebn!B2)*WAHL(ISTZAHL(Data!$C:$C)+1;0;Data!$C:$C))
aber angesichts der ohnehin schon suboptimalen Performance ...

Grüße
bastla
Member: Avalon1
Avalon1 Sep 26, 2010 at 09:08:07 (UTC)
Goto Top
Hello

hab die Array Formel jetzt eingebunden und es hat nach ein paar Anfangsschwierigkeiten
alles soweit super funktioniert !!

ich danke Euch!!

die Performance ist halt so eine Sache, aber da kann man nix machen....
muss man halt ein paar Minütchen warten face-smile

lg
ava
Mitglied: 76109
76109 Sep 26, 2010 at 09:46:59 (UTC)
Goto Top
Hallo ava!

Wenn Du das Ganze per VBA-Code machst, dann ließe sich das doch optimierenface-wink
    Const FormelText = "=SUM((Data!$A$2:$A$%1=A%2)*(Data!$B$2:$B$%1=B%2)*Data!$C$2:$C$%1)"  

   '........  
    
    With Sheets("Data")  
        EndZeile = .Cells(.Rows.Count, "A").End(xlUp).Row  
    
        SumFormel = Replace(FormelText, "%1", EndZeile)  
       
       '........  
        Sheets("Ziel").Cells(Zeile, "C").FormulaArray = Replace(SumFormel, "%2", Zeile)  
    End With
   
   '........

Gruß Dieter
Member: bastla
bastla Sep 26, 2010 at 09:53:05 (UTC)
Goto Top
Hallo Dieter!

Daran hatte ich zwar auch gedacht, aber dann wäre es ja vermutlich sinnvoller, gleich die gesamte Umsetzung in VBA vorzunehmen ...

Grüße
bastla
Mitglied: 76109
76109 Sep 26, 2010 at 10:04:54 (UTC)
Goto Top
Hallo bastla!

Ja, ich war eigentlich der Meinung, dass die Zusammenfassungs-Tabelle per VBA generiert wird?

Das Ganze könnte dann in etwa so aussehen:
Const StartZeile = 2

Const FormelText = "=SUM((Data!$A$2:$A$%1=A%2)*(Data!$B$2:$B$%1=B%2)*Data!$C$2:$C$%1)"  

Sub DatenSammeln()
    Dim Wks As Worksheet, Zeile As Long, EndZeile As Long, c As Range, i As Long, SumFormel As String

    Set Wks = ThisWorkbook.Worksheets("Ergebnis")  
    
    Wks.Cells.Clear
        
    With ThisWorkbook.Worksheets("Data")  
       .Range("A1:C1").Copy Wks.Range("A1")  
    
        Zeile = StartZeile:  EndZeile = .Cells(.Rows.Count, "A").End(xlUp).Row  
        
        SumFormel = Replace(FormelText, "%1", EndZeile)  
        
        For Each c In .Range("A:A")  
            If IsEmpty(c) Then Exit For
            
            If c.Row >= StartZeile Then
                For i = StartZeile To Zeile - 1
                    If c = Wks.Cells(i, "A") And c.Offset(0, 1) = Wks.Cells(i, "B") Then Exit For  
                Next
                
                If i = Zeile Then
                    Range(c, c.Offset(0, 1)).Copy Wks.Cells(Zeile, "A")  
                    Wks.Cells(Zeile, "C").FormulaArray = Replace(SumFormel, "%2", Zeile)  
                    Zeile = Zeile + 1
                End If
            End If
        Next
    End With
End Sub

Gruß Dieter
Member: bastla
bastla Sep 26, 2010 at 11:50:49 (UTC)
Goto Top
Hallo Dieter!

Meine Überlegung wäre: Wozu eine Formel eintragen, wenn sich auch gleich das Ergebnis ermitteln lässt (die Daten sollten lt Beispiel sogar schon sortiert sein) - die Formel ist, eben wegen der Optimierung des Bereichs, ja nicht dynamisch (genug), sodass jedenfalls bei einer Änderung der Datenmenge das VBA-Programm ohnehin neuerlich ausgeführt werden müsste ...

... daher zB ein Dictionary mit der Kombination Datum + LKW als Key und der Gewichtssumme als Wert befüllen und daraus die Ergebnistabelle erstellen oder, wenn die Daten tatsächlich sortiert sind/werden dürfen, einfach eine Gruppenverarrbeitung vornehmen ...

Grüße
bastla
Mitglied: 76109
76109 Sep 26, 2010 at 12:47:18 (UTC)
Goto Top
Hallo bastla!

Was soll ich sagenface-smile Bei der Summen-Formel, hatte ich eigentlich schon an Deinen Einwand gedacht, dass bei Änderung einer Gewichtsangaben das Makro erneut ausgeführt werden muss. Jezt ist Dein Einwand entgegen meiner Vorstellung genau andersherumface-smile Die Betrachtungsweisen sind ja recht vielseitig. Von daher keine Ahnung welches jetzt der beste Lösungsansatz ist.

Zudem gefällt mir Deine Array-Formel so gut, dass ich die garnicht missen möchteface-wink

Gruß Dieter
Member: bastla
bastla Sep 26, 2010 at 12:55:00 (UTC)
Goto Top
Hallo Dieter!
Von daher keine Ahnung welches jetzt der beste Lösungsansatz ist.
Geht mir ja auch so - ich bin einfach von der Annahme (bestärkt durch den Kommentar von Avalon1: "muss man halt ein paar Minütchen warten face-smile") ausgegangen, dass es sich um ein Reporting handelt, das eher größere Zeiträume abdeckt (und entsprechend seltener durchgeführt wird) - dann wird sich wohl an den Daten vermutlich nicht mehr viel ändern ...

Zudem gefällt mir Deine Array-Formel so gut, dass ich die garnicht missen möchteface-wink
Na dann ... face-wink

Grüße
bastla
GermansolvedQuestionVB for ApplicationsDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 Comments