Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

VBA: Daten nach Begriff durchsuchen und Werte kum.

VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 11:10:54
Josef
Hallo allerseits,
ich stehe vor einer Herausforderung, welche ich gerne mit einem Excel-Makro lösen möchte. Ich bin was VBA angeht noch ein Rookie und hoffe daher, hier Hilfe zu finden.
Die Beispieldatei gibt euch einen Überblick, über das was ich möchte:
https://www.herber.de/bbs/user/113948.xlsx
Im Tabellenblatt "Daten" findet ihr die Rohdaten (wichtig sind eigentlich nur drei Spalten, weswegen alle anderen Spalten Füller sind).
In diesem Blatt sind Buchungsdaten vorhanden. Nun möchte ich gerne dass ein Makro das Blatt Zeile für Zeile durchgeht und in Spalte F ("Beschreibung") nach bestimmten Begriffen Ausschau hält.
Wenn es einen der gesuchtne Begriffe findet, soll es den dazugehörigen Betrag (Spalte G) nehmen und kumuliert in das Tabellenblatt "Ausgabe" in die richtige Spalte und in die richtige Zeile (also mit dem passenden Namen) eintragen.
Leider sind die Namen weder im Daten-Tabellenblatt noch im Ausgabe-Tabellenblatt in irgendeiner Form sortiert.
Ich hoffe diese Erklärung ist ausreichend. Falls aber noch Fragen bestehen - immer her damit.
Beste Grüße
Jupp

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hilfsspalte und Pivot
01.06.2017 11:21:58
Fennek
Hallo,
wie wäre es eine Hilfsspalte mit den Suchbegriffen ("Auto" usw) im Sheet"Daten" anzulegen/errechnen und das Blatt Ausgabe durch eine Pivot-Tabelle zu ersetzen?
mfg
AW: Hilfsspalte und Pivot
01.06.2017 11:37:44
Josef
Hi Fennek,
danke für deine Antwort.
Das ist mir tatsächlich auch in den Sinn gekommen. Leider führt an der Ausgaben-Tabelle aber kein Weg vorbei. Sie muss enthalten sein und gefüllt werden.
Angesichts dessen, dass im realen Fall die Daten-Tabelle locker mal über 5.000 Zeilen haben kann, könnte die Pivot-Lösung auch umständlich werden.
Besten Gruß
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 11:29:13
yummi
Hallo Jupp,
dann findest du in deienr Beschreibung auf dem Datenblatt irgenwo immer entweder Auto, Fahhrad, U-Bahn oder Flugzeug, was den ndie Spalte festlegt? Was soll denn passieren wenn die Begriffe, so wie in deiern Bspdatei in der Beschreibung nicht gefunden werden?
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 11:34:20
Josef
Grüß dich yummi,
danke für deine rasche Antwort.
Sollte keiner der gesuchten Begriffe in der Beschreibung zu finden sein, sollte nichts passieren.
Wichtig sind nur die Werte der Zeilen, in denen die Begriffe in der Beschreibung auch zu finden sind.
Gruß
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 12:14:53
yummi
Hallo Jupp
den folgenden Code in ein Modul deiner Datei speichern:

Option Explicit
Function BestimmeLetzteZeile(ByVal wks As Worksheet, ByVal s As Integer) As Long
BestimmeLetzteZeile = wks.Cells(wks.Rows.Count, s).End(xlUp).Row
End Function
Function FindeWert(ByVal wks As Worksheet, ByVal strRange As String, ByVal strWert As String)  _
As Range
Set FindeWert = wks.Range(strRange).Find(strWert)
End Function
Function BestimmeLetzteSpalte(ByVal wks As Worksheet, ByVal z As Long) As Integer
BestimmeLetzteSpalte = wks.Cells(z, 256).End(xlToLeft).Column
End Function
Function Beschleunigen(ByVal BGesetzt As Boolean)
BGesetzt = Not BGesetzt
With Application
.ScreenUpdating = BGesetzt
.AskToUpdateLinks = BGesetzt
.EnableEvents = BGesetzt
.Calculation = BGesetzt
.DisplayAlerts = BGesetzt
End With
End Function
Sub Start()
Dim lastDaten As Long
Dim wkb As Workbook
Dim wksdaten As Worksheet
Dim wksAusg As Worksheet
Dim i As Long
Dim j As Integer
Dim rng As Range
Dim ispalte As Integer
Dim lzeile As Long
Set wkb = ThisWorkbook
Set wksdaten = wkb.Sheets("Daten")
Set wksAusg = wkb.Sheets("Ausgabe")
Beschleunigen True
lastDaten = BestimmeLetzteZeile(wksdaten, 6)
For i = 2 To lastDaten
ispalte = 0
For j = 7 To 10
If InStr(1, wksdaten.Cells(i, 6).Value, wksAusg.Cells(1, j).Value, vbTextCompare)  _
0 Then
ispalte = j
Exit For
End If
Next j
lzeile = 0
Set rng = FindeWert(wksAusg, "A:A", wksdaten.Cells(i, 21).Value)
If Not rng Is Nothing Then
lzeile = rng.Row
End If
If ispalte  0 And lzeile  0 Then
wksAusg.Cells(lzeile, ispalte).Value = wksAusg.Cells(lzeile, ispalte).Value +  _
wksdaten.Cells(i, 7).Value
End If
Next i
Beschleunigen False
End Sub
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 13:37:52
Josef
Hi yummi,
wow, vielen Dank! Es funktioniert einwandfrei. Ich bin hin und weg.
Nur noch eine Frage: gibt es eine Möglichkeit die 4 gesuchten Begriffe (es sind immer 4) auch als Konstante im Code festzulegen?
Nochmals vielen Dank!
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 13:53:45
yummi
Hallo Jupp,
ja ist möglich, ;-)
der entscheidende Teil ist

For j = 7 To 10
If InStr(1, wksdaten.Cells(i, 6).Value, wksAusg.Cells(1, j).Value, vbTextCompare)  _
_
0 Then
ispalte = j
Exit For
End If
Next j
du könntest es sogar variable machen und auf einem Extrablatt eine tabelle anlegen mit deinen Begriffen und in der nächsten Spalte eine zuordnung der Spalte
zsb etwa
Auto 7
Fahrrad 8
U-Bahn 9
Flugzeug 10
irgend etwas anderes 6
dann brauchst du nciht mal die Reihenfolge gleich lassen.
mal angenommen dein Blatt heisst config und deine liste steht in A und B

Option Explicit
Function BestimmeLetzteZeile(ByVal wks As Worksheet, ByVal s As Integer) As Long
BestimmeLetzteZeile = wks.Cells(wks.Rows.Count, s).End(xlUp).Row
End Function
Function FindeWert(ByVal wks As Worksheet, ByVal strRange As String, ByVal strWert As String)   _
_
As Range
Set FindeWert = wks.Range(strRange).Find(strWert)
End Function
Function BestimmeLetzteSpalte(ByVal wks As Worksheet, ByVal z As Long) As Integer
BestimmeLetzteSpalte = wks.Cells(z, 256).End(xlToLeft).Column
End Function
Function Beschleunigen(ByVal BGesetzt As Boolean)
BGesetzt = Not BGesetzt
With Application
.ScreenUpdating = BGesetzt
.AskToUpdateLinks = BGesetzt
.EnableEvents = BGesetzt
.Calculation = BGesetzt
.DisplayAlerts = BGesetzt
End With
End Function
Sub Start()
Dim lastDaten As Long
dim lastConfig as Long
Dim wkb As Workbook
Dim wksdaten As Worksheet
Dim wksAusg As Worksheet
dim wksconf as Worksheet
Dim i As Long
Dim j As Integer
Dim rng As Range
Dim ispalte As Integer
Dim lzeile As Long
Set wkb = ThisWorkbook
Set wksdaten = wkb.Sheets("Daten")
Set wksAusg = wkb.Sheets("Ausgabe")
Set wksconf = wkb.Sheets("config")
Beschleunigen True
lastDaten = BestimmeLetzteZeile(wksdaten, 6)
lastConfig = BestimmeLetzteZeile(wksconf, 1)
For i = 2 To lastDaten
ispalte = 0
For j = 1 To lastConfig
If InStr(1, wksdaten.Cells(i, 6).Value, wksConf.Cells(j,1).Value, vbTextCompare)   _
_
0 Then
ispalte = wksConfig.Cells(j,2).value
Exit For
End If
Next j
lzeile = 0
Set rng = FindeWert(wksAusg, "A:A", wksdaten.Cells(i, 21).Value)
If Not rng Is Nothing Then
lzeile = rng.Row
End If
If ispalte  0 And lzeile  0 Then
wksAusg.Cells(lzeile, ispalte).Value = wksAusg.Cells(lzeile, ispalte).Value +  _
wksdaten.Cells(i, 7).Value
End If
Next i
Beschleunigen False
End Sub
ich hoffe, ich hab kein Syntax error drin, hab das jetzt hier eingegeben. Also neues Blatt config anlegen und dort in A deine Begriffe und in B die zugehörige Spalte anlegen.
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 13:47:08
Josef
Oh und mir ist noch etwas eingefallen (sorry), woran ich nicht gedacht hatte:
Die Namen in Spalte U sind nicht in Stein gemeißelt, es können je nach Daten welche verschwinden und neue hinzukommen, die noch nicht im Ausgabeblatt enthalten sind.
Die einzige Lösung die mir da jetzt spontan einfällt ist, das Ausgabeblatt vorher mit den Namen zu befüllen.
Gruß
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 14:06:36
yummi
Hallo Jupp,
die Namen in U sind doch egal, das makro nimmt den Namen aus U und sucht ihn in Ausgabe Spalte A. Wenn er dort nciht vorhanden ist, wird auch keien Zeile gefunden, in die eingetragen wird. Du kannst also jeden beliebigen Wert in U schreiben, musst diesen dann nur in Ausgabe Spalte A ebenfalls aufführen
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 14:21:36
Josef
Hey yummi,
nochmals danke.
Genau das meinte ich ja. Ich hatte gehofft das Makro kann auch direkt dafür sorgen, dass die Namen aus dem "Daten"-Blatt auch im "Ausgabe"-Blatt vorkommen.
Es kann nämlich sein dass es da Abweichungen gibt. Idealerweise sollte das Ausgabe-Blatt also anfangs keine Namen enthalten.
Ich habe daher folgendes hinzugefügt:
Sheets("Daten").Select
Sheets("Daten").Columns("U:U").Select
Selection.Copy
Sheets("Ausgabe").Select
Sheets("Ausgabe").Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$A$14259").RemoveDuplicates Columns:=1, Header:= _
xlYes
Sieht grob aus, ich weiß. Nicht sehr elegant.
Im Grunde kopiert das Makro jetzt im Vorfeld die Namen aus den Daten, fügt diese in das Ausgabe-Blatt ein und löscht dann Duplikate.
Das tuts.
Gruß
Jupp
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 14:28:26
yummi
Hallo Jupp,
wenn der Name nciht gefunden wird in Ausgabe, dann unten dran hängen:

Option Explicit
Function BestimmeLetzteZeile(ByVal wks As Worksheet, ByVal s As Integer) As Long
BestimmeLetzteZeile = wks.Cells(wks.Rows.Count, s).End(xlUp).Row
End Function
Function FindeWert(ByVal wks As Worksheet, ByVal strRange As String, ByVal strWert As String)    _
_
_
As Range
Set FindeWert = wks.Range(strRange).Find(strWert)
End Function
Function BestimmeLetzteSpalte(ByVal wks As Worksheet, ByVal z As Long) As Integer
BestimmeLetzteSpalte = wks.Cells(z, 256).End(xlToLeft).Column
End Function
Function Beschleunigen(ByVal BGesetzt As Boolean)
BGesetzt = Not BGesetzt
With Application
.ScreenUpdating = BGesetzt
.AskToUpdateLinks = BGesetzt
.EnableEvents = BGesetzt
.Calculation = BGesetzt
.DisplayAlerts = BGesetzt
End With
End Function
Sub Start()
Dim lastDaten As Long
dim lastConfig as Long
dim lastAusg as Long
Dim wkb As Workbook
Dim wksdaten As Worksheet
Dim wksAusg As Worksheet
dim wksconf as Worksheet
Dim i As Long
Dim j As Integer
Dim rng As Range
Dim ispalte As Integer
Dim lzeile As Long
Set wkb = ThisWorkbook
Set wksdaten = wkb.Sheets("Daten")
Set wksAusg = wkb.Sheets("Ausgabe")
Set wksconf = wkb.Sheets("config")
Beschleunigen True
lastDaten = BestimmeLetzteZeile(wksdaten, 6)
lastConfig = BestimmeLetzteZeile(wksconf, 1)
lastAusgabe = BestimmeLetzteZeile(wksAusg, 1)
For i = 2 To lastDaten
ispalte = 0
For j = 1 To lastConfig
If InStr(1, wksdaten.Cells(i, 6).Value, wksConf.Cells(j,1).Value, vbTextCompare)   _
_
_
0 Then
ispalte = wksConfig.Cells(j,2).value
Exit For
End If
Next j
lzeile = 0
Set rng = FindeWert(wksAusg, "A:A", wksdaten.Cells(i, 21).Value)
If Not rng Is Nothing Then
lzeile = rng.Row
else
'Wert nicht gefunden, also anhängen
lzeile = lastAusgabe  +1
wksAusgabe.Cells(lzeile, 1).value = wksdaten.Cells(i, 21).Value
lastAusgabe  = lastAusgabe  +1
End If
If ispalte  0 And lzeile  0 Then
wksAusg.Cells(lzeile, ispalte).Value = wksAusg.Cells(lzeile, ispalte).Value +  _
wksdaten.Cells(i, 7).Value
End If
Next i
Beschleunigen False
End Sub
Jetzt wird der Wert bei nciht gefunden ergänzt ;-)
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 14:36:06
Josef
Hello again,
ich weiß, ich hatte vorhin gesagt dass mit Werten, in denen kein gesuchter Begriff auftaucht nichts geschehen soll (auch dafür sorry) ...
aber könnte man es auch so machen, dass in Spalte F des Ausgabe-Blatts der Wert ohne gesuchte Begriffe zu finden ist? So dass Spalte F bis J quasi den Gesamtwert pro Name liefern?
Sorry nochmal und beste Grüße
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 15:10:54
yummi
Hallo Jupp,
im Prinzip kann man alles machen
Jetzt hast Du es so, dass deine Suchbegriffe mit Spaltenzuordnung im Tabellenblatt config variabel sind und wenn der Wert aus Daten Spalte U in Ausgabe Spalte A nciht zu finden ist, wird dieser unten dran gehängt, ansonsten wird in die Zeile geschrieben mit dem gleichen Wert Daten!U = Ausgabe!A
Und Du willst du diesen Wert, der ja jetzt unten ergänzt wurde noch zusätzlich in F schreiben oder was hast du vor?
Ich versteh dich nicht ganz
Gruß
yummi
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 15:27:40
Josef
Hallo nochmal,
sorry falls ich mich nicht ganz verständlich ausgedrückt habe.
Also: momentan ist es so, dass in 4 Spalten (G - J im Ausgabe-Blatt) die kumulierten Werte rausgesucht werden, je nachdem ob die gesuchten Begriffe in der Beschreibung auftauchen (Spalte F im Daten-Blatt).
Also in Zeile 3, Spalte G steht der Wert von "Auto" für diesen Namen
in Zeile 3, Spalte G steht der Wert von "Fahrrad" für diesen Namen
So weit, so gut.
Nun möchte ich, dass in Spalte F der Differenz-Wert steht. Also quasi der Gesamtwert, abzüglich der Werte in Spalte G - J. So dass die Spalten F - J den Gesamtwert des Namens ergeben.
Also wenn das Makro keinen der gesuchten Begriffe in einer Beschreibung findet, soll dieser Wert in Spalte F kumuliert werden (ich hatte vorhin gesagt dass mit solchen Daten nichts geschehen soll, das war aber falsch, sorry nochmal).
Ich hoffe das ist verständlicher. Ich wüsste nicht wie man das lösen kann (außer mit einer Pivot).
Gruß
Jupp
Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 15:54:08
yummi
Hallo Jupp,
hier erstmal die Datei mit der Konfigurierbarkeit auf Config, damit wir vom gleichen Stand ausgehen.
Am besten Du machst mal ein Beispiel fertig was passieren soll, einfach händisch eintragen mit Kommentaren dazu.
Probier bitte auch mal aus, was passiert, wenn du in U einen neuen, bisher unbekannten Namen eingibst.
Ich kann mir das dann evtl morgen mal anschauen, heute wird das nix mehr.
Gruß
yummi
Die Datei:

Die Datei https://www.herber.de/bbs/user/113962.xlsm wurde aus Datenschutzgründen gelöscht


Anzeige
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 16:31:03
Josef
Erneut Hallo,
vielen Dank für die Datei. Ich habe im Datenblatt einen Namen hinzugefügt und er wurde erfolgreich in die Liste der Namen im Ausgabe-Blatt übernommen.
Mir ist wohl aufgefallen, dass Änderungen im Daten-Blatt im Ausgabe-Tabellenblatt nur teilweise erfolgen.
Wenn ich in den Daten eine neue Zeile mit einem neuen Namen hinzufüge, wird dieser Name dem Ausgabe-Blatt hinzugefügt (wie es auch sein soll).
Wenn ich aber eine Zeile lösche oder den Namen überschreibe, bleibt sie bei erneuter Durchführung des Makros trotzdem im Ausgabe-Blatt drin.
Aber das könnte man ja dadurch bereinigen, indem das Makro das Ausgabe-Tabellenblatt zu Beginn der Durchführung komplett löscht.
Bezüglich der Spalte, welche die Differenz enthalten soll - anbei eine Beispiel-Datei die vielleicht verdeutlicht, was ich möchte:
https://www.herber.de/bbs/user/113964.xlsm
In Spalte F des Ausgabe-Blatts soll quasi der Wert stehen, der sich den Spalten G - J nicht zuordnen lässt.
Mal angenommen dass es im Daten-Blatt drei Zeilen gibt, welche den Namen"Fielmann" in Spalte U haben:
Beschreibung	Betrag	Name
Bla Auto	10	Fielmann
Bla Bla		15	Fielmann
Bla U-Bahn	5	Fielmann
Dann müsste nach Durchführung des Makros im Ausgabe-Blatt die Zeile mit dem Namen "Fielmann" folgendermaßen aussehen:
Name		Differenz	Auto	Fahrrad	U-Bahn	Flugzeug
Fielmann	15		10		5		
Wenn man sich alle Werte der Zeile ansieht, erhält man für "Fielmann" also 30, den Gesamtwert für diesen Namen.
Ich hoffe es ist verständlich. Ich habe noch Kommentare in die Beispiel-Datei eingefügt.
Schönen Abend wünsche ich noch
Jupp
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
01.06.2017 22:57:50
yummi
Hallo Jupp,
die Änderung ist nicht so groß. Es gibt im Code eine Zeile

ispalte = 0

vor der for j Schleife. Dort musst Du anstatt der 0 eine 6 (Spalte F) schreiben, dann wird der Wert immer in Spalte F geschrieben, wenn nix gefunden wurde.
Ich hoffe die Kommentare im Code helfen dir ihn zu verstehen.
Ansonsten kann ich dir nur den Tip geben Step by Step (F8) zu debuggen und dir anzuschauen welche Werte in den Variablen stehen.
Gruß
yummi
AW: VBA: Daten nach Begriff durchsuchen und Werte kum.
02.06.2017 07:48:42
Josef
Schönen guten Morgen!
jetzt ist alles paletti, den Rest und ggf. kleine Anpassungen schaff ich ab hier alleine.
Nochmals vielen Dank und ein schönes langes Wochenende.
Gruß
Jupp

340 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige