Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Mit Makro Werte in anderer Tabelle updaten

Mit Makro Werte in anderer Tabelle updaten
Lutz
Hallo Excel-Profis,
ich komme alleine nicht weiter bzw. finde den Anfang nicht.
Ich habe eine Auswertung mit Rechnungen in der bei Zahlungseingang dann in Tabelle1 etwas eingegeben wird.
Danach wird per Makro dieser Datensatz in eine Tabelle "Liste" übertragen.
Im Blatt "Liste" stehen die ganzen einzelnen Datensätze.
Bei diesen Datensätzen soll jetzt in Spalte AA "bez" eingetragen werden.
Aber nur für die Rechnung die jetzt als bezahlt markiert wird- die Rechnungsnummer ist im Blatt "Liste" in Spalte D.
D.h. das Makro müßte wie folgt ablaufen:
1. in "Tabelle1" steht in Zelle AA1 die Rechnungsnummer. z.B. 100
2. Das Makro wechselt in das Blatt "Liste" durchsucht die Spalte D nach der 100 und schreibt bei denen Sätzen die 100 haben ein "bez" in Spalte AA
Wenn es geht sollte man sicherheitshalber auch 2 Kriterien angeben können z.B. Rechnung 100 und Position (Spalte AB im Blatt "Tabelle1"; Spalte E im Blatt "Liste).
Da ja immer alle Zeilen im Blatt Liste durchlaufen werden müssen, muß das halbwegs schnell sein - also wohl erst auf Nichtübereinstimmung prüfen und dann nächster Satz?
Vielen Dank im voraus für Eure Hilfe.
Viele Grüße Lutz
AW: Mit Makro Werte in anderer Tabelle updaten
21.10.2011 16:20:19
guentherh
Hallo Lutz,
Schau mal ob Dir das im Anhang schnell genug ist.
Hab mal für 50 Re und ca 180 Positionen ein Beispiel gemacht
https://www.herber.de/bbs/user/77135.xls
Viel Erfolg damit,
Günther
AW: Mit Makro Werte in anderer Tabelle updaten
21.10.2011 19:52:29
lutz
Hallo Günther,
vielen lieben Dank.
Funktioniert gut! Leider habe ich ziemlich viele Datensätze und wenn man es mal auf 65000 Datensätze im Blatt Liste füllt, dauert es ziemlich lange.
Ich habe es schon mal etwas gentunt (siehe appliation...):

Sub Bezahlt_markieren2()
Dim RechnungsFeld, Listenfeld As Object
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.Calculation = xlManual
ActiveWorkbook.Sheets("Liste").Activate
ActiveWorkbook.Sheets("Liste").Activate
For Each RechnungsFeld In Worksheets("Tabelle1").Range("AA1:AA" & Worksheets("Tabelle1").Cells. _
SpecialCells(xlCellTypeLastCell).Row)
For Each Listenfeld In ActiveSheet.Range("D2:D" & ActiveSheet.Cells.SpecialCells( _
xlCellTypeLastCell).Row)
If Listenfeld.Text = RechnungsFeld.Text Then
If Listenfeld.Offset(0, 1) = RechnungsFeld.Offset(0, 1) Then
ActiveSheet.Range("AA" & Listenfeld.Row) = "bez"
End If
End If
Next Listenfeld
Next RechnungsFeld
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
Ich habe leider auch nicht so viel Plan, ich denke, das mit dem select bei der Tabelle frißt wohl Leistung?!
Vielleicht sieht sich das noch mal ein "Performance-Profi" an?
Vielleicht ist es schneller beide Bedingungen in eine Zeile zu packen?
Ich habe irgednwo auch mal gelesen, dass es schneller ist, wenn man erst das abfragt was meistens nicht zutrifft - also das der Suchbegriff dem Inhalt der Zelle ist?
In jedem Fall schon mal vielen lieben Dank für Deine Mühe
Viele Grüße Lutz
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 13:14:12
Reinhard
Hallo Lutz,
prübier mal in einer Mappenkopie dieses

Option Explicit
Sub Bezahlt_markieren2()
Dim ZeiL As Long
Call Langsam(False)
Const Formel As String = "=IF(AND(Tabelle1!AA1=D1,Tabelle1!AB1=E1),""bez"","""")"
With ActiveWorkbook.Worksheets("Liste")
ZeiL = .Cells(.Rows.Count, 4).End(xlUp).Row
.Range("AA1:AA" & ZeiL).Formula = Formel
.Range("AA1:AA" & ZeiL).Value = .Range("AA1:AA" & ZeiL).Value
End With
Call Langsam(True)
End Sub
Sub Langsam(ByVal EinAus As Boolean)
With Application
.ScreenUpdating = EinAus
.EnableEvents = EinAus
.DisplayAlerts = EinAus
.Calculation = IIf(EinAus, xlManual, xlAutomatic)
End With
End Sub

Gruß
Reinhard
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 13:33:21
lutz
Hallo Reinhard,
vielen Dank - geht superschnell aber er tut nichts in Blatt Liste -
hier mal die Beispieldatei - Dein Makro ist Nummer3
https://www.herber.de/bbs/user/77147.xls
Viele Grüße Lutz
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 15:41:01
Daniel
Hi
hier mal 2 Varianten:
die erste ist geeignet für kleine Datenmengen (maximal 16.000 Zeilen)
die zweite sollte auch bei sehr grossen Datenmengen zügig funktionieren, allerdings ist es hierfür erforderlich, die Spalten AA-AB der Tabelle 1 zu sortieren (das ist im Makro drin):

Sub Bezahlt_markieren4()
'Für kleiner Datenmengen, Sortierung bleibt erhalten
Dim ZeiL As Long
Const Formel As String = "=If(E1=VLookUp(d1,Tabelle1!AA:AB,2,0),""bez"","""")"
With ActiveWorkbook.Worksheets("Liste")
ZeiL = .Cells(.Rows.Count, 4).End(xlUp).Row
With .Range("AA1:AA" & ZeiL)
.Formula = Formel
.Value = .Value
On Error Resume Next
.SpecialCells(xlCellTypeConstants, 16).ClearContents
On Error GoTo 0
End With
End With
End Sub
Sub Bezahlt_markieren5()
'für Grössere Datenmengen, Tabelle1 Spalte AA:AB wird für schneller Verarbeitung sortiert
Dim ZeiL As Long
Const Formel As String =  "=IF(D1=Vlookup(D1,Tabelle1!AA:AA,1,1),If(E1=VLookUp(d1,Tabelle1!AA: _
AB,2,1),""bez"",""""),"""")"
Sheets("Tabelle1").Range("AA:AB").Sort Key1:=Sheets("Tabelle1").Range("AA1"), order1:= _
xlAscending, Header:=xlNo
With ActiveWorkbook.Worksheets("Liste")
ZeiL = .Cells(.Rows.Count, 4).End(xlUp).Row
With .Range("AA1:AA" & ZeiL)
.Formula = Formel
.Value = .Value
End With
End With
End Sub

Beide Makros sind mit deiner Beispieldatei getestet.
die Zeilenumbüche im Code ( Unterstrich) musst du von Hand entfernen, so daß die Codezeile auch wieder eine Textzeile ist.
Gruß Daniel
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 21:00:04
lutz
Hallo Daniel,
vielen Dank, das ist schon ziemlich schnell.
Ich habe als Variante 6 noch mal die Speed-Befehle rein und wieder raus genommen.
Ich habe nur ein Problem: er schreibt mir nach dem Makro in die Überschrift Spalte AA in Zeile eins ein #NV rein.
https://www.herber.de/bbs/user/77153.xls
Weiß Du warum?
Vielen Dank auch an Reinhard, ihr habt mir schon sehr geholfen
Viele Grüße Lutz
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 21:03:35
lutz
Hallo Daniel,
vielen Dank, das ist schon ziemlich schnell.
Ich habe als Variante 6 noch mal die Speed-Befehle rein und wieder raus genommen.
Ich habe nur ein Problem: er schreibt mir nach dem Makro in die Überschrift Spalte AA in Zeile eins ein #NV rein.
https://www.herber.de/bbs/user/77153.xls
Weiß Du warum?
Vielen Dank auch an Reinhard, ihr habt mir schon sehr geholfen
Viele Grüße Lutz
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 22:47:42
Daniel
Hi
naja, wenn da in der ersten Zeile ein Überschift ist, dann darfst du die Formel einfach nicht in die erste Zeile schreiben, sondern erst in die zweite:

With .Range("AA2:AA" & ZeiL)

gleichermassen musst du die Formel anpassen, da jetzt die Formel nicht in die erste, sonden in die zweite Zeile geschrieben wird, dh im Formelstring musst du aus D1 und E1 dann D2 und E2 machen.
Gruß, Daniel
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 23:00:00
lutz
Hallo Daniel,
da war ich wohl etwas blind - die AA2 und D2 hatte ich schon probiert aber den E1 wohl übersehen...
Vielen Dank und noch einen schönen Rest-Sonntag Abend
Ganz viele Grüße aus dem hohen Norden Lutz
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 17:10:09
Reinhard
Hallo Lutz,
Daniel hat dir ja wohl schon Lösungen gezeigt.
Mir geht um etwas anderes. Du schreibst es tut sich nix.
Okay, kann passieren.
Lösche oder kommentiere die Zeile aus wo steht
.Value=.Value
Dann bleiben die Formeln erhalten und bei Excel gut müßtest du rausfinden warum die Formeln nicht funktionieren.
Und falls du das nicht weißt,
.Value=.Value
in Vba sieht seltsam aus, was soll es bringen einen Weert durch den gleichen zu ersetzen?
Der Hintergrund ist, die Formel der Zelle wird entfernt, es steht nur noch der errechnte Wert drinnen.
Gruß
reinhard
Anzeige
AW: Mit Makro Werte in anderer Tabelle updaten
23.10.2011 21:01:15
lutz
Hallo Reinhard,
vielen Dank für die Rückmeldung - habe auf die andere Antwort von Daniel noch mal geantwortet.
Viele Grüße Lutz

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige