Top-Themen

Aktuelle Themen (A bis Z)

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit

gelöst VBA - Worksheet (Spalte) durchsuchen und Werte auslesen

Mitglied: Avalon1

Avalon1 (Level 1) - Jetzt verbinden

24.09.2010, aktualisiert 07:00 Uhr, 12790 Aufrufe, 13 Kommentare

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)


01.
Private Sub DatenSammeln() 
02.
 
03.
    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A1") = "Datum" 
04.
    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("B1") = "LKW" 
05.
    Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("C1") = "Gewicht" 
06.
     
07.
    Dim Datum As Date 
08.
    Dim TempDatum As Date 
09.
     
10.
    Dim LKW As String 
11.
    Dim TempLKW As String 
12.
     
13.
    Dim Gewicht As Long 
14.
    Dim TempGewicht As Long 
15.
     
16.
    Dim Zaehler As Long 
17.
    Zaehler = 2 
18.
    Dim Zaehler2 As Long 
19.
    Zaehler2 = 2 
20.
     
21.
    While Not IsEmpty(Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A" + Trim(Str(Zaehler)))) 
22.
     
23.
        Datum = Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("A" + Trim(Str(Zaehler))) 
24.
        LKW = Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("B" + Trim(Str(Zaehler))) 
25.
                 
26.
'      DAS IST DIE SCHLEIFE DIE ICH BIS DATO HABE     
27.
'        While Datum = Workbooks("Auswertung.xls").Worksheets("Data").Range("A" + Trim(Str(Zaehler2))) 
28.
'               TempLKW = Workbooks("Auswertung.xls").Worksheets("Data").Range("B" + Trim(Str(Zaehler2))) 
29.
'               If LKW = TempLKW Then 
30.
'                           TempGewicht = Workbooks("Auswertung.xls").Worksheets("Data").Range("C" + Trim(Str(Zaehler2))) 
31.
'                          Gewicht = Gewicht + TempGewicht 
32.
'               End If 
33.
'               Zaehler2 = Zaehler2 + 1 
34.
'        Wend 
35.
36.
'        Workbooks("Auswertung.xls").Worksheets("Ergebnis").Range("C" + Trim(Str(Zaehler))) = Gewicht 
37.
'        Gewicht = 0 
38.
         
39.
        Zaehler2 = 2 
40.
         
41.
        Zaehler = Zaehler + 1 
42.
         
43.
    Wend 
44.
     
45.
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

lg
ava
Mitglied: bastla
24.09.2010 um 08:16 Uhr
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
Bitte warten ..
Mitglied: Avalon1
24.09.2010 um 10:09 Uhr
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
Bitte warten ..
Mitglied: 76109
25.09.2010 um 12:31 Uhr
Hallo bastla!

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

Gruß Dieter
Bitte warten ..
Mitglied: bastla
25.09.2010 um 13:49 Uhr
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
Bitte warten ..
Mitglied: 76109
25.09.2010 um 14:06 Uhr
Hallo bastla!

Aaaaah danke, jetzt funktionierts bei mir auch 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-Formeln
Bitte warten ..
Mitglied: bastla
25.09.2010 um 14:15 Uhr
Hallo Dieter!
muss wohl an der Überschrift gelegen haben?
Konkret: der Spalte C (die hatte ich tatsächlich beim Testen nicht eingegeben ) ...

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
Bitte warten ..
Mitglied: Avalon1
26.09.2010 um 11:08 Uhr
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

lg
ava
Bitte warten ..
Mitglied: 76109
26.09.2010 um 11:46 Uhr
Hallo ava!

Wenn Du das Ganze per VBA-Code machst, dann ließe sich das doch optimieren
01.
    Const FormelText = "=SUM((Data!$A$2:$A$%1=A%2)*(Data!$B$2:$B$%1=B%2)*Data!$C$2:$C$%1)" 
02.
 
03.
   '........ 
04.
     
05.
    With Sheets("Data") 
06.
        EndZeile = .Cells(.Rows.Count, "A").End(xlUp).Row 
07.
     
08.
        SumFormel = Replace(FormelText, "%1", EndZeile) 
09.
        
10.
       '........ 
11.
        Sheets("Ziel").Cells(Zeile, "C").FormulaArray = Replace(SumFormel, "%2", Zeile) 
12.
    End With 
13.
    
14.
   '........
Gruß Dieter
Bitte warten ..
Mitglied: bastla
26.09.2010 um 11:53 Uhr
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
Bitte warten ..
Mitglied: 76109
26.09.2010 um 12:04 Uhr
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:
01.
Const StartZeile = 2 
02.
 
03.
Const FormelText = "=SUM((Data!$A$2:$A$%1=A%2)*(Data!$B$2:$B$%1=B%2)*Data!$C$2:$C$%1)" 
04.
 
05.
Sub DatenSammeln() 
06.
    Dim Wks As Worksheet, Zeile As Long, EndZeile As Long, c As Range, i As Long, SumFormel As String 
07.
 
08.
    Set Wks = ThisWorkbook.Worksheets("Ergebnis") 
09.
     
10.
    Wks.Cells.Clear 
11.
         
12.
    With ThisWorkbook.Worksheets("Data") 
13.
       .Range("A1:C1").Copy Wks.Range("A1") 
14.
     
15.
        Zeile = StartZeile:  EndZeile = .Cells(.Rows.Count, "A").End(xlUp).Row 
16.
         
17.
        SumFormel = Replace(FormelText, "%1", EndZeile) 
18.
         
19.
        For Each c In .Range("A:A") 
20.
            If IsEmpty(c) Then Exit For 
21.
             
22.
            If c.Row >= StartZeile Then 
23.
                For i = StartZeile To Zeile - 1 
24.
                    If c = Wks.Cells(i, "A") And c.Offset(0, 1) = Wks.Cells(i, "B") Then Exit For 
25.
                Next 
26.
                 
27.
                If i = Zeile Then 
28.
                    Range(c, c.Offset(0, 1)).Copy Wks.Cells(Zeile, "A") 
29.
                    Wks.Cells(Zeile, "C").FormulaArray = Replace(SumFormel, "%2", Zeile) 
30.
                    Zeile = Zeile + 1 
31.
                End If 
32.
            End If 
33.
        Next 
34.
    End With 
35.
End Sub
Gruß Dieter
Bitte warten ..
Mitglied: bastla
26.09.2010 um 13:50 Uhr
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
Bitte warten ..
Mitglied: 76109
26.09.2010 um 14:47 Uhr
Hallo bastla!

Was soll ich sagen 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 andersherum 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öchte

Gruß Dieter
Bitte warten ..
Mitglied: bastla
26.09.2010 um 14:55 Uhr
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 ") 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öchte
Na dann ...

Grüße
bastla
Bitte warten ..
Ähnliche Inhalte
VB for Applications
Mehrere Worksheets durchsuchen
gelöst Frage von Gimli3311VB for Applications2 Kommentare

Guten Tag Zusammen, Zu meinem Problem: Ich öffne eine Datei und suche in der nach Begriffen. Im jetzigen Fall ...

Microsoft Office

Excel-VBA: Alle Werte einer Spalte in einer anderen Suchen

gelöst Frage von KevinPMicrosoft Office4 Kommentare

Hallo, ich hänge jetzt schon seit Tagen dran und komm nicht zu Rande. Ich versuche jeden Wert eines Tabellenblattes ...

Microsoft Office

Excel VBA Wenn Wert in Spalte A Werte und Formeln in weitere Spalten eintragen

gelöst Frage von ExxiStMicrosoft Office3 Kommentare

Hallo zusammen, Wenn in Spalte A "Ergebnis" steht, sollen wie unten beschrieben Formeln in die gleiche Zeile der danebenstehenden ...

Microsoft Office

Excel Spalte nach Wort durchsuchen

gelöst Frage von pcklickMicrosoft Office2 Kommentare

Hallo liebe Admins! Ich habe ein Problem: Und zwar möchte ich eine Excel-Spalte nach einem Wort durchsuchen, bzw. zählen ...

Neue Wissensbeiträge
Windows 10

Microcode-Updates KB4090007, KB4091663, KB4091664, KB4091666 für Windows 10

Information von kgborn vor 6 StundenWindows 101 Kommentar

Kurze Information für Administratoren von Windows 10-Systemen, die mit neueren Intel CPUs laufen. Microsoft hat zum 23. April 2018 ...

iOS
Updates für Iphone und Co
Information von sabines vor 9 StundeniOS

Gestern abend ist iOS 11.3.1 erschienen, ein kleineres Update, dass einige Lücken schließt und "Lahmlegen" nach einem Display Tausch ...

Windows 7

Windows 7 - Server 2008 R2: Exploit für Total Meltdown verfügbar

Information von kgborn vor 1 TagWindows 7

Kleine Information für Administratoren, die für die Updates von Windows 7 SP1 und Windows Server 2008 R2 SP1 verantwortlich ...

Sicherheit

Zero Day-Schwachstelle im Internet Explorer - wird von APT bereits ausgenutzt

Information von kgborn vor 2 TagenSicherheit

Im Kernel des Internet Explorer scheint es eine Zero Day-Lücke zu geben, die von staatlichen Akteuren (APT) im Rahmen ...

Heiß diskutierte Inhalte
Batch & Shell
Powershell: Im AD nach Rechnern mit bestimmten IP-Adressen suchen
gelöst Frage von Raven42Batch & Shell35 Kommentare

Hallo zusammen, ich suche nach einer Möglichkeit nach Computern im AD zu suchen , deren IP-Adresse mit 10.11.12. beginnt. ...

C und C++
Frage1 C Programmierung-Makefile Frage2 PHP-Programmierung HTTP-Fehler 404
Frage von KatalinaC und C++34 Kommentare

Hallo, ich habe 2 Fragen, die nichts miteinander zu tun haben aber mit denen ich mich gerade beschäftige: 1. ...

LAN, WAN, Wireless
Watchguard T15 VPN Einrichtung
gelöst Frage von thomasjayLAN, WAN, Wireless25 Kommentare

Hallo zusammen, wir möchten gerne über unsere Watchguard T15 einen VPN-Tunnel (Mobile VPN with IPSec) einrichten! Als Client nutzen ...

Batch & Shell
Mit Powershell den Inhalt einer Excel mit einer Text Datei abgleichen
gelöst Frage von Bommi1961Batch & Shell21 Kommentare

Hallo zusammen, ich muss den Inhalt einer Excel Datei (Mappe1) mit dem Daten einer Text Datei abgleichen. Die Daten ...