Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

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

Frage Entwicklung VB for Applications

Mitglied: Avalon1

Avalon1 (Level 1) - Jetzt verbinden

24.09.2010, aktualisiert 07:00 Uhr, 11941 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 ..
Neuester Wissensbeitrag
Microsoft

Lizenzwiederverkauf und seine Tücken

(5)

Erfahrungsbericht von DerWoWusste zum Thema Microsoft ...

Ähnliche Inhalte
Microsoft Office
Excel Dateien durchsuchen und Werte einzeln in neue Excel Datei auslesen (1)

Frage von krischanii zum Thema Microsoft Office ...

Datenbanken
Spalte in einer SQL-Tabelle auslesen. Werte durch Tab getrennt! (4)

Frage von Janekr77 zum Thema Datenbanken ...

Microsoft Office
gelöst Verschieben von Zellinformation in andere Spalte (per VBA) excel 2010 (5)

Frage von thomas1972 zum Thema Microsoft Office ...

Heiß diskutierte Inhalte
LAN, WAN, Wireless
FritzBox, zwei Server, verschiedene Netze (18)

Frage von DavidGl zum Thema LAN, WAN, Wireless ...

Windows Netzwerk
Windows 10 RDP geht nicht (16)

Frage von Fiasko zum Thema Windows Netzwerk ...

Windows Server
Outlook Verbindungsversuch mit Exchange (15)

Frage von xbast1x zum Thema Windows Server ...