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

Frage Entwicklung VB for Applications

GELÖST

VBA - Worksheet (Spalte) durchsuchen und Werte auslesen

Mitglied: Avalon1

Avalon1 (Level 1) - Jetzt verbinden

24.09.2010, aktualisiert 07:00 Uhr, 12640 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
Linux

Meltdown und Spectre: Linux Update

Information von Frank vor 2 TagenLinux

Meltdown (Variante 3 des Prozessorfehlers) Der Kernel 4.14.13 mit den Page-Table-Isolation-Code (PTI) ist nun für Fedora freigegeben worden. Er ...

Tipps & Tricks

Solutio Charly Updater Fehlermeldung: Das Abgleichen der Dateien in -Pfad- mit dem Datenobject ist fehlgeschlagen

Tipp von StefanKittel vor 3 TagenTipps & Tricks

Hallo, hier einmal als Tipp für alle unter Euch die mit der Zahnarztabrechnungssoftware Charly von Solutio zu tun haben. ...

Sicherheit

Meltdown und Spectre: Wir brauchen eine "Abwrackprämie", die die CPU-Hersteller bezahlen

Information von Frank vor 3 TagenSicherheit12 Kommentare

Zum aktuellen Thema Meltdown und Spectre: Ich wünsche mir von den CPU-Herstellern wie Intel, AMD oder ARM eine Art ...

Sicherheit

Meltdown und Spectre: Realitätscheck

Information von Frank vor 3 TagenSicherheit12 Kommentare

Die unangenehme Realität Der Prozessorfehler mit seinen Varianten Meltdown und Spectre ist seit Juni 2017 bekannt. Trotzdem sind immer ...

Heiß diskutierte Inhalte
Firewall
Penetrationstester-Labor - Firewalls
Frage von Oli-nuxFirewall10 Kommentare

Mich würde interessieren warum man beim Einrichten eines Penetrationstester-Labor (VMs) die Firewall der Systeme deaktivieren soll? Hat das nur ...

SAN, NAS, DAS
Wer kennt sich mit QNAP und CISCO aus ?
gelöst Frage von MachelloSAN, NAS, DAS9 Kommentare

Hallo Zusammen hier im Forum, Ich habe ein QNas 451+ und dieses NAS hat zwei GBit Lan Adapter die ...

Netzwerkgrundlagen
IPv6 Inter-VLAN Routing
gelöst Frage von clSchakNetzwerkgrundlagen9 Kommentare

Hi ich befasse mich gerade mit der Implementierung von IPv6 was bisher (in einem VLAN) korrekt funktioniert inkl. DNS ...

Windows Netzwerk
Zugriff auf den Desktop Ordner eines anderen Rechners in der gleichen Domäne
gelöst Frage von JensNomaWindows Netzwerk6 Kommentare

Guten Abend, ich war neulich mit unserem Admin am Tisch gesessen. Er an seinem Notebook angemeldet mit dem Domänen-Admin, ...