PDA

Vollständige Version anzeigen : Zellwert über Index aus zweiter Tabelle holen und in erste (aktuelle) zurückschreiben


FrankAs
24.08.2017, 06:37
Hallo!

Ich - Anfänger in Sachen VBA - muss Folgendes umsetzen:

Ich habe zwei Excel-Dateien:

- kunden.xlsx -----> Kundennummer in Spalte A
- rechnungen.xlsx -> Kundennummer in Spalte E / Rechnungsdatum in Spalte B


In kunden.xlsx muss ich in Spalte D das jüngste Rechnungsdatum eintragen, das zu einer Kundennummer in rechnungen.xlsx gefunden wird.

Zu beachten: Es kann mehrere Rechnungen zu einer Kundennummer geben, es gibt aber auch Kundennummern, zu denen es keine Rechnung gibt.

Wie kann ich das lösen?

Toll wäre ein Code, der bereits meine "Individualitäten" (Dateinamen und Spalten) enthält, da ich, wie gesagt, Anfänger bin und ich mir so einen
gewissen Lernerfolg und den Aha-Effekt davon verspreche :idee:

Gruß
Frank

Oge
24.08.2017, 08:47
Hallo Frank,

warum VBA? Reicht nicht eine Formel?

FrankAs
24.08.2017, 09:10
Hallo Frank,

warum VBA? Reicht nicht eine Formel?

Hallo Helmut,

ich würde das gern per VBA lösen, um die Vorgehensweise bei solcher immer wiederkehrender Problematik zu verstehen:

Aber mal abgesehen davon: Wie würde denn die Formel in Excel aussehen? Sprengt die nicht den Rahmen? :grins:

Bin echt gespannt!

Gruß
Frank

Oge
24.08.2017, 11:09
Hallo Frank,

A) Formellösung:
hier ein Beispiel für eine mögliche Formel in Zeile 2:
=WENNFEHLER(AGGREGAT(15;6;([rechnungen.xlsx]Tabelle1!$B$2:$B$6)/([rechnungen.xlsx]Tabelle1!$E$2:$E$6=A2);1);"")

B) Fragen zu VBA:
Ich nehme an, dass in einem Programm nicht einfach die oben aufgeführte Formel ausgeführt werden soll, richtig?
Können die beiden Listen nach Kundennummer sortiert sein?

FrankAs
24.08.2017, 12:41
Hallo Helmut,

vielen Dank! Ich habe die Formel mal an meine Gegebenheiten angepasst, und sie funktioniert einwandfrei! Ich habe die Funktion bei AGGREGAT noch von
15 auf 14 geändert (möchte ja das jüngste Datum haben) und die letzte Zeile des zu durchsuchenden Bereichs angegeben, und ich habe exakt, was ich brauche.

Schön wäre es natürlich trotzdem, wenn mir dazu noch jemand den Code schicken könnte, um diese Formel in VBA umzusetzen.

Eine Frage habe ich dazu aber noch!

So sieht meine Formel jetzt aus:

=WENNFEHLER(AGGREGAT(14;6;([rechnungen.xlsx]rechnungen!$B$2:$B$16917)/([rechnungen.xlsx]rechnungen!$E$2:$E$16917=A2);1);"")

Hier wird in Spalte E von Zeile 2 bis 16917 gesucht (aktuelle Anzahl Zeilen in rechnungen.xlsx).
Kann man $16917 so ändern, dass grundsätzlich die letzte benutzte Zeile herangezogen wird?

Oge
24.08.2017, 15:42
Hallo Frank,

zu:Schön wäre es natürlich trotzdem, wenn mir dazu noch jemand den Code schicken könnte, um diese Formel in VBA umzusetzen.

hier eine Version:Option Explicit
Private Sub cbTuwat_Click()
Dim lngZeile As Long
Dim lngLZeile As Long
Dim varArr As Variant
Dim wbR As Workbook
Dim wsR As Worksheet
Dim wsK As Worksheet
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
Set wbR = Workbooks("rechnungen.xlsx")
Set wsR = wbR.Sheets("Tabelle1")
Set wsK = ThisWorkbook.Sheets("Tabelle1")

'---------------------------
' Rechnungen einlesen
'---------------------------
lngLZeile = wsR.Cells(Rows.Count, 2).End(xlUp).Row
varArr = wsR.Range("B2:E" & lngLZeile)
For lngZeile = 1 To UBound(varArr, 1)
If varArr(lngZeile, 4) <> "" Then
If dict.exists(varArr(lngZeile, 4)) Then
If varArr(lngZeile, 1) > dict(varArr(lngZeile, 4)) Then
dict(varArr(lngZeile, 4)) = varArr(lngZeile, 1)
End If
Else
dict.Add varArr(lngZeile, 4), varArr(lngZeile, 1)
End If
End If
Next lngZeile
'---------------------------
' in Kundenliste eintragen
'---------------------------
lngLZeile = wsK.Cells(Rows.Count, 1).End(xlUp).Row
varArr = wsK.Range("A2:B" & lngLZeile)
For lngZeile = 1 To UBound(varArr, 1)
If dict.exists(varArr(lngZeile, 1)) Then
varArr(lngZeile, 2) = dict(varArr(lngZeile, 1))
Else
varArr(lngZeile, 2) = ""
End If
Next lngZeile
wsK.Range("A2:B" & lngLZeile) = varArr

Set dict = Nothing
Set wbR = Nothing
Set wsR = Nothing
Set wsK = Nothing
End Sub


zu:Kann man $16917 so ändern, dass grundsätzlich die letzte benutzte Zeile herangezogen wird?
Wenn du mit benannten Tabellen arbeitest (Strg+T) werden in den Bezügen auf alle Einträge einer Spalte nicht die Addressen, sondern der Name der Tabelle und Spalte eingetragen.
Zusätzliche Zeilen, aber auch zusätzlich eingebaute Spalten werden dann berücksichtigt.

FrankAs
06.09.2017, 13:45
Möchte nur noch Danke sagen :top: