Werte aus anderer Tabelle als Array

  • Excel

Es gibt 6 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    Werte aus anderer Tabelle als Array

    Hallo zusammen,

    ich habe diese Frage schon in einem anderen Forum gestellt, allerdings konnte mir da keiner wirklich helfen.
    Zum Hintergrund: Ich möchte mein VBA Programm wartungsfreundlicher machen. Im Code habe ich an manchen Stellen Begriffe hart codiert (weils nicht anders möglich ist). Daher ist meine Idee eine Exceldatei anzulegen, wo man die hart codierten Begriffe untereinander reinschreibt und beim Programmaufruf als Array genutzt und in andere Makros verwendet wird. Dann muss man nicht durch knapp 500 Zeilen gucken und jede hart codierte Stelle anpassen.

    Ein einfaches Beispiel kann sowas sein:
    - Tabelle1 mit allen Ländern der Welt
    - Tabelle2 mit allen Industrieländern der Welt
    In Tabelle1 soll die Hauptauswertung ablaufen. Die Industrieländer der Tabelle2 sollen in ein Array gespeichert werden. Tabelle1 greift auf das Array zu und löscht z.B. alle anderen Länder, sodass nur die Industrieländer in der Liste aller Länder vorhanden sind.
    Jetzt kann es passieren, dass Industrieländer dazukommen. Damit man nicht im Code von Tabelle1 die hartcodierten Zeilen ändern muss, soll man lediglich die neuen Industrieländer in die Tabelle2 schreiben und beim nächsten Ausführen soll Tabelle1 auf das aktualisierte Array zugreifen können und die neue Liste mit den neuen Industrieländern ausgeben können.
    Natürlich macht dieses Beispiel kein Sinn, es soll es aber vereinfacht und anschaulich darstellen :-).

    Ich habe schon was in Foren gefunden, dass sieht dann in etwa so aus:

    VB.NET-Quellcode

    1. Public fVarWerte As Variant
    2. Sub test1()
    3. Dim wbQuelle As Workbook
    4. Set wbQuelle = Workbooks.Open("J:Arbeitsmapp.xlsx")
    5. fVarWerte = wbQuelle.Worksheets(1).UsedRange.Value
    6. wbQuelle.Close savechanges:=False
    7. Set wbQuelle = Nothing
    8. End Sub


    Allerdings funktioniert dies noch nicht an jeder Stelle im Code (halt nur da wo explizit ein Array abgefragt wird).

    In diesem Beispiel läuft das Makro zwar, aber es werden kompletten falsche Werte wiedergegeben:

    VB.NET-Quellcode

    1. For Each c In Worksheets("Tabell1").UsedRange
    2. Select Case c.Value
    3. Case fVarWerte
    4. [color=#008000]' Vorher: "xxx", "yyy", "zzz", "dddd", "eeee", "tttt", "oooo", "pppp"[/color]
    5. Sheets("Tabelle2").Select
    6. c.EntireRow.Copy Destination:=Worksheets("Tabelle2").Rows(i)
    7. i = i + 1
    8. End Select
    9. Next c


    Daher meine Frage: Liegt das an dem Case-Statement, dass das Array nicht richtig benutzt wird? Gibt es eine andere Methode bzw. gibt es einen Weg wie ich ein Array auf das Case-Statement anwende?

    Schonmal vielen Dank für die Hilfe!

    VG

    tim12051999 schrieb:

    For Each c In Worksheets("Tabell1").UsedRange
    Läuft durch ALLE Zellen (in UsedRange).
    Wenn ich dich richtig verstehe, willst du nur die erste Spalte durchlaufen und abhängig von deren Wert die Zeile kopieren.

    Visual Basic-Quellcode

    1. Set ws = Worksheets("Tabell1")
    2. For Each c In Intersect(ws.UsedRange,ws.Range("A:A"))
    3. ...
    4. Next


    tim12051999 schrieb:

    fVarWerte = wbQuelle.Worksheets(1).UsedRange.Value
    ergibt ein zweidimensionales Array.
    Ggf. musst du das vorher transponieren, um es eindimensional zu machen.
    Schau dir das Array im Debugger an.


    tim12051999 schrieb:

    Select Case c.Value
    Case fVarWerte
    Was willst du damit erreichen?
    Willst du wissen, ob c.Value im Array fVarWerte enthalten ist?

    Der Ansatz mit der Hilfstabelle ist schon ganz OK.
    Aber das würde ich nicht erst in ein Array verwandeln, sondern einfach in der Excel-Tabelle nachschauen.

    In der Lookup-Tabelle (Tabelle2):

    Visual Basic-Quellcode

    1. Function ContainsCountry(ByVal Country As String) As Boolean
    2. Dim c as Range
    3. Set c = Range("A:A").Find(Country, LookIn:=xlValues, LookAt:=xlWhole)
    4. CountainsCountry = Not c Is Nothing
    5. End Function

    In der AuswertTabelle (Tabelle1):

    Visual Basic-Quellcode

    1. Sub DeleteUnusedRows
    2. Dim r as Long, c as Range
    3. For r = Cells(Rows.Count,1).End(xlUp).Row To 1 Step -1 'rückwärts druchlaufen
    4. Set c = Cells(r,1)
    5. If Not Tabelle2.CountainsCountry(c.Value) Then
    6. c.EntireRow.Delete xlUp
    7. End If
    8. Next
    9. End Sub

    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 7 mal editiert, zuletzt von „petaod“ ()

    Wenn ich dich richtig verstehe, willst du nur die erste Spalte durchlaufen und abhängig von deren Wert die Zeile kopieren.Visual Basic-Quellcode

    VB.NET-Quellcode

    1. Set ws = Worksheets("Tabell1")
    2. For Each c In Intersect(ws.UsedRange,ws.Range("A:A"))
    3. ...
    4. Next


    Ah ja stimmt, ergibt Sinn. In der Spalte A stehen alle Einträge die mit dem "Array" verglichen werden soll, allerdings stehen in der Spalte B noch Zahlen, die mit rüberkopiert werden soll. Dies könnte man ja eigentlich dann mit Offset lösen. Sollte also kein Problem sein.

    VB.NET-Quellcode

    1. tim12051999 schrieb:fVarWerte = wbQuelle.Worksheets(1).UsedRange.Value

    ergibt ein zweidimensionales Array.Ggf. musst du das vorher transponieren, um es eindimensional zu machen.Schau dir das Array im Debugger an.

    Bin noch sehr unerfahren in VBA deswegen müsste ich erstmal nachgucken, was ein zweidimensionales Array ist und wie man es transponiert. :D


    VB.NET-Quellcode

    1. Select Case c.Value
    2. Case fVarWerte

    Was willst du damit erreichen?Willst du wissen, ob c.Value im Array fVarWerte enthalten ist?


    Meine eigentlicher Gedanke dahinter: Das Makro geht mit einer For schleife durch Tabelle1 und guckt bei jedem Wert, ob dieser Wert in dem Array (Tabelle2) vorhanden ist. Wenn ja dann kopiert das Makro diesen rüber. Wenn nicht, dann nimmt er sich den nächsten Eintrag vor usw.



    Der Ansatz mit der Hilfstabelle ist schon ganz OK.Aber das würde ich nicht erst in ein Array verwandeln, sondern einfach in der Excel-Tabelle nachschauen.In der Lookup-Tabelle (Tabelle2):Visual Basic-Quellcode

    VB.NET-Quellcode

    1. Function ContainsCountry(ByVal Country As String) As Boolean
    2. Dim c as Range
    3. Set c = Range("A:A").Find(Country, LookIn:=xlValues, LookAt:=xlWhole)
    4. CountainsCountry = Not c Is Nothing
    5. End Function


    In der AuswertTabelle (Tabelle1):Visual Basic-Quellcode

    VB.NET-Quellcode

    1. Sub DeleteUnusedRows
    2. Dim r as Long, c as Range
    3. For r = Cells(Rows.Count,1).End(xlUp).Row To 1 Step -1 'rückwärts druchlaufen
    4. Set c = Cells(r,1)
    5. If Not Tabelle2.CountainsCountry(c.Value) Then
    6. c.EntireRow.Delete xlUp
    7. End If
    8. Next
    9. End Sub



    Diesen Ansatz werde ich gleich mal probieren und melde mich dann zurück.

    Schonmal vielen Dank für deine Antwort!
    VG
    Hallo petaod,

    Da die 2 Tabellen in verschiedenen Dateien sind habe ich versucht das so einzubauen:

    VB.NET-Quellcode

    1. ​Sub DeleteUnusedRows()
    2. Dim r As Long, c As Range
    3. Dim wkb1 As Workbook
    4. Set wkb1 = Workbooks.Open("J:Arbeitsmappe.xlsm")
    5. For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1 'rückwärts druchlaufen
    6. Set c = Cells(r, 1)
    7. If Not wkb1.Sheets("Tabelle1").CountainsCountry(c.Value) Then
    8. c.EntireRow.delete xlUp
    9. End If
    10. Next
    11. wkb1.Close savechanges:=False
    12. Set wkb1 = Nothing
    13. End Sub


    Kannst du mir sagen, was ich da falsch mache?
    1) Unterscheide zwischen dem Blattnamen und dem Objektnamen.
    wkb1.Sheets("Tabelle1") ist vom Typ Worksheet.
    Tabelle1 ist ein Objekt vom Typ Tabelle1, das von Worksheet geerbt hat.
    Den Namen des Objekts siehst du im Editor (spätestens wenn du F4 drückst).
    Dort kannst du es auch in eine vernünftige Bezeichnung umbenennen.

    2) Den Code auf zwei Workbooks zu verteilen, ist keine gute Idee.
    In dem Fall verlagerst du ContainsCountry-Funktion besser in ein lokales Worksheet oder Modul und übergibst das fremde Worksheet-Objekt.

    Visual Basic-Quellcode

    1. Function ContainsCountry(Byval ws As Worksheet, ByVal Country As String) As Boolean
    2. Dim c as Range
    3. Set c = ws.Range("A:A").Find(Country, LookIn:=xlValues, LookAt:=xlWhole)
    4. CountainsCountry = Not c Is Nothing
    5. End Function

    Visual Basic-Quellcode

    1. ​Sub DeleteUnusedRows
    2. Dim r as Long, c as Range, wb as Workbook, ws as Worksheet
    3. Set wb = Workbooks.Open("J:\CountryList.xlsx")
    4. Set ws = wb.Sheets("Industrial")
    5. For r = Cells(Rows.Count,1).End(xlUp).Row To 1 Step -1 'rückwärts durchlaufen
    6. Set c = Cells(r,1)
    7. If Not ContainsCountry(ws,c.Value) Then c.EntireRow.Delete xlUp
    8. Next
    9. wb.Close False
    10. End Sub
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    Hallo,

    ich komme trotz der umfassenden Hilfe leider nicht auf das richtige Ergebnis.
    Mal ein anderer Gedanke:
    Ich habe jetzt die Liste "Industrial" in der Auswertungs Excel-Datei drinne, sodass alles in der selben Datei ist (verschiedene Tabellenblätter). Die Liste "Industrial sieht dann so in etwa aus:
    A1: Land1
    B1: Land2
    C1: Land3
    D1: ...

    Kann ich irgendwie alle Werte aus der Tabelle einer Public Variablen zuweisen, sodass ich in nachfolgenden Markos nur die Variable ansprechen kann und nicht die Länder einzeln hart codieren muss? Das heißt ähnlich wie bei dir oben im Beispiel: Dass ich mit einem Case oder If-Statement die Variable anspreche und dann etwas damit gemacht wird z.b. rauslöschen oder kopieren? Ich kann ehrlich gesagt mit deiner Funktion nicht so viel anfangen, da ich bisher nur mit Subs gearbeitet habe :-/.

    tim12051999 schrieb:

    Die Liste "Industrial sieht dann so in etwa aus:
    A1: Land1
    B1: Land2
    C1: Land3
    Es ist ungewöhnlich, das alles in eine Zeile zu schreiben, normal wäre eher in einer Spalte.
    Dann funktioniert auch die Funktion.
    Oder eine Tabelle mit Zeilen und Spalten

    Quellcode

    1. AllCountries | Industrial | Europe
    2. Afghanistan | USA | Germany
    3. Pakistan | Canada | France
    4. ...


    tim12051999 schrieb:

    Kann ich irgendwie alle Werte aus der Tabelle einer Public Variablen zuweisen
    Mit dem Ansatz bist du doch schon mal kläglich gescheitert.

    tim12051999 schrieb:

    Ich kann ehrlich gesagt mit deiner Funktion nicht so viel anfangen, da ich bisher nur mit Subs gearbeitet habe
    Das ist kein Grund.
    Falls du nicht mal die absoluten Grundlagen lernen willst, müsste ich mich aus dem Thread zurück ziehen.
    Falls du dazu bereit bist, können wir dich schrittweise zum Erfolg führen.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 2 mal editiert, zuletzt von „petaod“ ()