Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA u.a. dynamischer Bereich

Forumthread: VBA u.a. dynamischer Bereich

VBA u.a. dynamischer Bereich
26.12.2021 19:31:10
Claudia
Hallo liebe Forummitglieder,
vor einiger Zeit habt ihr mir wunderbar geholfen, u.a. per VBA Daten zu übertragen. Diese Test-Datei habe ich auch angehängt.
Meine Original-Datei hat sich aber nun verändert und das Programm (Aufrufbar über ein Button) hängt sich auf (Eieruhr). Es sind Tabellenblätter hinzugekommen, es wurden dynamische Bereiche und Bereichsnamen definiert usw. Allerdings haben diese neuen Blätter rein gar nichts mit der Datenübertragung zu tun. Lösche ich diese neuen Blätter wieder, funktioniert die Übertragung.
Würdet ihr euch den Code einmal ansehen bitte?
Iim Reg. "Aufgaben" habe ich eine detaillierte Beschreibung hinterlegt.
Freue mich sehr über eure Unterstützung. Vielen Dank.
https://www.herber.de/bbs/user/149997.xlsm
Claudia
Anzeige

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA u.a. dynamischer Bereich
26.12.2021 20:19:31
ralf_b
Hallo Claudia,
der Fehler ist nicht nachvollziehbar. Der Code sieht ok aus. Soll heißen das die benannten Tabellenblätter in Variablen übernommen wurden und nurn diese verwendet werden. Wodurch eine Verwechslung mit neuen Tabellenblättern nicht stattfinden kann.
Dein Hinweis auf die Bereichsnamen könnte die Fehlerursache dorthin verorten, da diese Bereichsnamen nicht in der Beispieldatei vorhanden sind. Hier steht die Vermutung im Raum das ein oder mehrere Namen mit Variablennamen oder Excelfunktionen kollidieren.
gruß
rb
Anzeige
AW: VBA u.a. dynamischer Bereich
26.12.2021 20:40:10
Claudia
Hallo Ralf,
vielen Dank für deine Antwort. Bereichsnamen sind eindeutig vergeben, z.B. Farbe_Schalter, oder Modell_Schalter und dynamische Bereiche als Namen: dyn_daten, dyn_hilfstabelle usw.
Ich baue mal die Testdatei um, schaue ob dann der "Fehler" auch auftritt und sende sie mal.
Danke.
Viele Grüße
Claudia
Anzeige
AW: VBA u.a. dynamischer Bereich
26.12.2021 22:18:30
Claudia
Hallo,
ich übernehme jetzt Blatt für Blatt in eine neue Arbeitsmappe, um zu testen, wo der Fehler liegt. Ist sehr aufwendig, da ich die Formeln anpassen muss. Werde mich dann morgen dazu melden (wobei, bis jetzt passt es - verrückt)
1.
Aber, kann dennoch jemand, oder Ralf? schauen, wie ich im VBA die Tabelle dynamisch anspreche? In der Testdatei ist sie noch nicht dyn. gesetzt, würde sie dan "dyn_daten" nennen.
2.
Ist die Artikel-Nr. bereits im Reg. Daten vohanden, dann die Bezeichnung in C nicht übertragen aber Nachfolger in B und Preis in H überschreiben mit den Daten aus der Preisliste wobei der Preis durch 100 dividiert werden muss. In der Preisliste gilt der Preis pro 100.
Freue mich natürlich über jede weitere Unterstützung und sage im voraus schon einmal DANKE.
Viele Grüße
Claudia
Anzeige
AW: VBA u.a. dynamischer Bereich
26.12.2021 22:35:28
ralf_b
ich weis nicht genau was du eigentlich meinst mit dynamisch ansprechen. Entweder meinst du intelligente Tabellen oder du meinst variable Bereichsgrößen.
Es gibt für alles eine Lösung. Aber so auf blauen Dunst wird das nichts mit Ratschlägen.
AW: VBA u.a. dynamischer Bereich
26.12.2021 22:43:18
Claudia
Hallo Ralf,
ja, ich meine "intelligente Tabelle" Strg T, und zwar für das Register DATEN.
Danke dir Ralf.
Viele Grüße
Claudia
Anzeige
AW: VBA u.a. dynamischer Bereich
27.12.2021 01:30:53
Matthias
Hallo Claudia,
wenn Du Deine intelligente Tabelle angelegt hast kannst Du die Adresse der Spalte so abfragen bzw. verwenden

MsgBox Range("dyn_daten[[Art. Nr.]]").Address
Gruß Matthias
AW: VBA u.a. dynamischer Bereich
27.12.2021 01:50:11
ralf_b
hier eine Variante mit der "dynamischen Tabelle"
Ich habe die Programmlogik etwas umgestellt und, aus meiner Sicht, vereinfacht.
Ich hoffe deine Anforderungen richtig verstanden zu haben. 

Sub DatenabgleichDynTab()
Dim TB3    As Worksheet
Dim  i As Long
Dim result, ArtNr
Dim objLstRows As ListRows 'liste aller Zeilen in dyn Tab
Dim obLstRow As ListRow ' einzelne Zeile in dyn Tab
Dim objLst As ListObject ' dyn Tab objekt
Set TB3 = Worksheets("Preisliste")
Set objLst = Worksheets("daten").ListObjects("dyn_daten")
Set objLstRows = objLst.ListRows
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To TB3.Cells(Rows.Count, 1).End(xlUp).Row 'preisliste
ArtNr = TB3.Cells(i, 1)
result = Application.Match(ArtNr, objLst.ListColumns(1).DataBodyRange, 0)
If IsError(result) Then
'nicht gefunden
Set obLstRow = objLstRows.Add
With obLstRow.Range
.Cells(1) = ArtNr
.Cells(1).Interior.Color = RGB(169, 208, 142) 'helleres Grün um schwarze Schriftzeichen besser lesen zu können 'vbGreen
.Cells(2) = TB3.Cells(i, 3)   'Bezeichnung
End With
Else
'wenn gefunden Zeilenobject setzen
Set obLstRow = objLstRows(result)
With obLstRow.Range
Select Case .Cells(2).Value
Case "ersatzlos", "Ersatzlos", "Kein Nachfolger", Empty
Case Else: .Cells(1).Interior.Color = vbYellow
End Select
End With
End If
obLstRow.Range.Cells(3) = TB3.Cells(i, 2) 'Nachfolger
obLstRow.Range.Cells(8) = TB3.Cells(i, 4) / 100 'Preis
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set objLstRows = Nothing
Set obLstRow = Nothing
Set objLst = Nothing
End Sub

Anzeige
AW: VBA u.a. dynamischer Bereich
27.12.2021 10:52:14
Claudia
Guten Morgen ihr Zwei,
ich komme erst am späten Nachmittag dazu, es auszuprobieren. Melde mich dann auf jeden Fall.
Vielen Dank an euch.
VG
Claudia
AW: VBA u.a. dynamischer Bereich
27.12.2021 20:25:09
Claudia
Hallo Ralf,
dein Code funktioniert super auf der Test-Datei. Dankeschön dafür.
Was noch nicht berücksichtigt ist:
Ist die Artikel-Nr. bereits im Reg. Daten vohanden, dann die Bezeichnung in C nicht übertragen aber Nachfolger in B und Preis in H überschreiben mit den Daten aus der Preisliste.
Wenn du Lust und Zeit hast, dieses in deinem Code noch umzusetzen, würde mich das natürlich freuen.
Viele Grüße
Claudia
Anzeige
AW: VBA u.a. dynamischer Bereich
27.12.2021 20:38:17
ralf_b
Wieso sollte das nicht umgesetzt sein?
.Cells(2) = TB3.Cells(i, 3) 'Bezeichnung wird nur in Zweig für "nicht gefunden" gesetzt.
Nachfolger und Preis in jedem Fall
AW: VBA u.a. dynamischer Bereich
27.12.2021 20:59:45
Claudia
Hey Ralf,
wenn ich im Reg. Daten, die Artikel-Bezeichnung (Sp 3) manuell überschreibe, anschließend die Preisliste einlese, überschreibt der Code die Artikel-Bezeichnung lt. Preisliste. Bei bereits vorhandenen Artikeln, soll die Artikelbezeichnung aber nicht überschrieben werden.
Hoffe, nerve nicht zu sehr. Danke.
VG
Claudia
Anzeige
AW: VBA u.a. dynamischer Bereich
27.12.2021 22:00:49
ralf_b
ok, kleiner Zahlendreher.
bitte testen. 

Sub DatenabgleichDynTab()
Dim TB3    As Worksheet
Dim i As Long
Dim result, ArtNr
Dim objLstRows As ListRows 'liste aller Zeilen in dyn Tab
Dim obLstRow As ListRow ' einzelne Zeile in dyn Tab
Dim objLst As ListObject ' dyn Tab objekt
Set TB3 = Worksheets("Preisliste")
Set objLst = Worksheets("daten").ListObjects("dyn_daten")
Set objLstRows = objLst.ListRows
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For i = 2 To TB3.Cells(Rows.Count, 1).End(xlUp).Row 'preisliste
ArtNr = TB3.Cells(i, 1)
result = Application.Match(ArtNr, objLst.ListColumns(1).DataBodyRange, 0)
If IsError(result) Then
'nicht gefunden
Set obLstRow = objLstRows.Add
With obLstRow.Range
.Cells(1) = ArtNr
.Cells(1).Interior.Color = RGB(169, 208, 142) 'helleres Grün um schwarze Schriftzeichen besser lesen zu können 'vbGreen
 .Cells(3).Value = TB3.Cells(i, 2).Value   'Bezeichnung
End With
Else
'wenn gefunden Zeilenobject setzen
Set obLstRow = objLstRows(result)
With obLstRow.Range
Select Case .Cells(2).Value
Case "ersatzlos", "Ersatzlos", "Kein Nachfolger", Empty
Case Else: .Cells(1).Interior.Color = vbYellow
End Select
End With
End If
   obLstRow.Range.Cells(2) = TB3.Cells(i, 3) 'Nachfolger
obLstRow.Range.Cells(8) = TB3.Cells(i, 4) / 100 'Preis
Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Set objLstRows = Nothing
Set obLstRow = Nothing
Set objLst = Nothing
End Sub

Anzeige
Funktioniert prima
27.12.2021 23:17:26
Claudia
Hallo Ralf,
jetzt funktioniert es so, wie ich es mir gewünscht habe (tolles Weihnachtsgeschenk :-)
Danke für deine Umsetzung und noch einen schönen Abend.
Viele Grüße
Claudia
PS: In der Originaldatei funktioniert es jetzt soweit, dass er fast 1 Std. zum Einlesen benötigt, bei gleicher Artikelanzahl. Aber definitiv muss das mit den Bereichsnamen und intelligenten Tabellen zu tun haben. Hier teste ich aber noch andere Möglichkeiten aus .
Anzeige
AW: Funktioniert prima
28.12.2021 00:16:50
ralf_b
jeder Zugriff auf Range ,Cells usw. ist natürlich zeitaufwendig.
mit Arrays wäre es bestimmt sehr viel schneller.
Aber mit Sicherheit kann man das aus der Ferne nicht sagen woran es bei der Originaldatei liegt. Wie viele Zeilen hast du denn da?
AW: Funktioniert prima
28.12.2021 09:34:54
Claudia
Guten Morgen Ralf,
es sind genauso viele Zeilen und Spalten wie in der Testdatei. Besteht evtl. die Möglichkeit, dir die Originaldatei zu schicken? Es sind zwar keine personenbezogene Daten darin aber möchte sie nicht so gerne öffentlich stellen. Umbauen geht leider nicht. Es sind lediglich 2 zusätzliche Tabellenblätter (1 Hilfstabelle mit Stammdaten und 1 Tabelle mit Formeln).
Wäre natürlich die einfachste Lösung für mich, weiß allerdings nicht, ob das hier erlaubt ist.
Vielen Dank für deine Mühe.
VG
Claudia
Anzeige
AW: Funktioniert prima
28.12.2021 11:44:39
ralf_b
meine email steht nicht umsonst in meinem Profil. Immer her damit.
AW: Funktioniert prima
28.12.2021 13:40:42
Claudia
oh ja super.
Danke schön.
VG
Claudia
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige