VBA Excel - Saplte um Wert ergänzen wenn in anderer Spalte nicht vorhanden

Phantro · 21. August 2015

Hi Leute,

ich bin gerade dabei ein recht umfangreiches Makro zu schreiben.
Soweit läuft auch alles recht gut - nur ein "Extra", welches ich einbauen möchte, bereitet mir Kopfschmerzen.

Tabellenaufbau:

Spalte A = Kürzel vom Standort, immer 3-stellig
Spalte M = Technischer Platz. Dieser setzt sich aus dem 3-stelligen Kürzel und weiteren Daten zusammen.

Mein Vorhaben:
Ich möchte, dass das Makro prüft, ob in Spalte M eine Zeile mit einem Kürzel beginnt, welches noch nicht in Spalte A steht und entsprechend am Ende der Spalte A einfügt.
Wenns bescheiden läuft, dann können auch mehrere Zeilen in M nicht in A vorhanden sein.

Ich bekomme es aktuell nicht mehr auf die Reihe, eine entsprechende Schleife zu bauen, die mir mein vorhaben umsetzt.

Könnt ihr mir bitte helfen?

Gruß
Phantro

Spike S. · 21. August 2015

Da du schon recht weit gekommen zu sein scheinst, spare ich mir mal die Code Schnipsel.
Ich würde es so machen: Erst Spalte A durchgehen und alle eindeutigen Werte (also keine doppelten Einträge) in einem Array speichern. Dann beim iterieren über Spalte M einfach prüfen ob die ersten 3 Zeichen aus dem Wert in M in dem Array bekannt sind. Wenn nicht, in A eintragen.
Dafür benötigt man lediglich eine kleine "inArray" Funktion die true oder false zurückgibt, sowohl für die "Bestandsaufnahme" in Spalte A, als auch beim prüfen gegen den Wert aus Spalte M.

Gruß
Spike

Phantro · 21. August 2015

Hallo Spike,

danke für den Tipp mit den Arrays. Gehört hab ich zwar schon von, aber bisher nie mit gearbeitet

Vor allem nicht mit dynamischen Arrays. Schließlich müsste ja nach jeder Ergänzung das Array erweitert werden und die Neuerung am Ende der Spalte A eingefügt werden.

Code:

lz2 = range("M:M").find("*",,,,xlbyrows,xlprevious).row       'findet die letzte, befüllte Zeile in Spalta A
For i = 1 to lz2 step1
lz = range("A:A").find("*",,,,xlbyrows,xlprevious).row          'findet die letzte, befüllte Zeile in Spalta A
arr = range("a1:a"&lz)                                                  'array gefüllt
if left(cells(i,13),3) 

next

Soweit müsste es dann theoretisch funktionieren oder? Nur wie weiter?

Spike S. · 21. August 2015

Nicht ganz. Du brauchst 2 separate Schleifen, eine um in Spalte A zu schauen, welche Werte es gibt und dann die eigentliche für Spalte M.
Dynamische Arrays werden ohne Längenangaben deklariert und können zur Laufzeit mit "ReDim" erweitert werden. Mit dem Zusatz "Preserve" werden bereits vorhandene Daten beibehalten, ohne wird das Array geleert.

Code:

Dim standorte as Variant
standorte = Array()
lz = range("A:A").find("*",,,,xlbyrows,xlprevious).row          'findet die letzte, befüllte Zeile in Spalta A'
for i = 1 to lz
    if not inArray(cells(i, 1).value, standorte) then
        ReDim Preserve standorte(UBound(standorte) + 1)
        standorte(UBound(standorte)) = cells(i, 1).value
    end if
next

Das ganze noch mal so ähnlich mit der Spalte M. Wenn die ersten 3 Zeichen aus dem Wert in Spalte M nicht im Array enthalten sind, lz um 1 erhöhen und diese 3 Zeichen in Spalte A, Zeile lz einfügen.

Die Funktion "inArray" ist kein Hexenwerk:

Code:

Function inArray(ByVal valueToBeFound As String, ByRef arrToSearch) As Boolean
    Dim searchIndex As Long, found As Boolean
    found = False
    For searchIndex = LBound(arrToSearch) To UBound(arrToSearch)
        If arrToSearch(searchIndex) = valueToBeFound Then
            found = True
        End If
    Next
    inArray = found
End Function

Gruß

Phantro · 21. August 2015

Oh man Spike, Du verknotest mir gerade das Hirn

UBound, LBound... Ich dachte, ich kann schon bissl was in Sachen Makros, aber das? Da muss ich mich nochmal hinter die Materie klemmen und schauen, was da was ist und kann. Werde die 2. Schleife versuchen selbst mal aufzubauen. Bin ich mal gespannt. Schonmal Danke soweit :-)

Spike S. · 21. August 2015

Ja, VBA ist nur auf den ersten Blick einfach. Wenn man bisschen mehr machen will, muss man sich bissl besser auskennen. VBA hat ein paar Eigenheiten
LBound = lower bound = erster Index des Arrays
UBound = upper bound = letzter Index des Arrays
Das nimmt man weil in VBA die Arrays beliebige Indexe haben können. Man kann ein Array auch so deklarieren:
arr( 2 to 10 )
Dadurch ist der erste Index nicht die 0 wie wenn man nur arr( 10 ) schreibt oder in den meisten anderen Sprachen, sondern die 2.

Wenn noch was unklar ist, helfe ich gern noch weiter.

Phantro · 21. August 2015

Ja, das stimmt. War schon froh als ich Makros in abhängigkeit von einander setzen konnte und Abgleiche etc. durchführen konnte, die mit Formeln nicht gingen. Um Arrays bin ich immer drum rum gekommen.

Kennst Du zufällig eine gute Tutorial Seite die das Thema bissl ausführlicher behandelt? Ich komm immer nur auf irgendwelche Foren wo sowas fertig da steht oder eben "Tutorials" wo nur überspitzt 2 Sätze zu stehen.

Bin mit der 2. Schleife noch nicht wirklich viel weiter =(

Spike S. · 21. August 2015

Aus dem FF kenne ich leider keine Tutorials-Seite. Habe mir VBA damals in der Schule angeeignet und danach autodikatisch ausgebaut. Entweder mit Google zu einzelnen Fragen oder der MSDN. Ich vermute, hier im Forum gibt es vielleicht schon einen passenden Thread der ein paar Seiten nennt, ansonsten einfach mal in die Runde fragen

Wie gesagt, die zweite Schleife ist ganz ähnlich:

Code:

lz2 = range("M:M").find("*",,,,xlbyrows,xlprevious).row          'findet die letzte, befüllte Zeile in Spalta M'
for i = 1 to lz2
    if not inArray(left(cells(i,13).value, 3), standorte) then
        lz = lz + 1
        cells(lz, 1).value = left(cells(i,13).value, 3)
    end if
next

Beachte, das lz von der ersten Schleife weiterverwendet wird und hochgezählt wird, um nach dem letzten Eintrag in A den Wert hinzuzufügen.

Phantro · 21. August 2015

Naja, ich hab Mitte letzen Jahres mit dem Quark angefangen um einzelne Arbeitsprozesse zu vereinfachen. Damals wusst ich ja gar nicht was vorne und hinten ist. Hab mich dann langsam auch durchgebissen. In der Schule hatte wir sowas leider nie =(

Ich danke Dir für die Mühen. Werd das Ganze mal einbauen und durchtesten und dann auf Tutorial-Suche gehen :-)

Edit:
Ich erhalte den Laufzeitfehler 438 - Objekt unterstützt die Methode oder Eigenschaft nicht.
Ich habe jeweils vor die Cells noch sheets(bla). stehen. Das sollte dem Ganzen doch nicht weh tun oder?
Die erste Schleife rennt sauber durch, bei der zweiten Schleife steigt er direkt bei deiner Codezeile 3 aus.

cumulonimbus8 · 21. August 2015

«workshets» statt «sheets»?
CN8

Phantro · 22. August 2015

Nein, das bringt die gleiche Fehlermeldung. Wenn ich (Work)Sheets weglasse, dann erhalte die Fehlermeldung, dass ein Objekt fehlen würde. Ich verstehe aktuell nur nicht wieso. Die Schleife klingt für mich plausibel. Nur VBA stört sich an irgendwas

EDIT: Wenn ich mit der Maus über Cells gehe, dann zeigt er mir den korrekten Inhalt an. Bei Sheets zeigt er mir auch den korrekten Namen an (Lasse diesen in vorher in eine Variable schreiben - funktioniert in anderen Codezeilen einwandfrei).

GMG-CC · 22. August 2015

Code:

Versuchs mal vom Prinzip her so:

Code:

Dim wksHogo as Worksheet 
Set wksHugo = ThisworkbookSheets("Hugo")

with wksHugo
   .Cells(2,3) = 123
   .Cells(5,7)=456
   .Range("C19")="Moin, Moin"
End With

Phantro · 22. August 2015

Hallo Günther,

danke für deinen Code.
Ich habe eben nochmal die Variante von Spike getest. Aus irgendeinem Grund hat es nun einwandfrei funktioniert. Ich habe allerdings nichts am Code verändert. Irgendwie seltsam. Aber es klappt nun alles, das ist die Hauptsache.

Allerdings hat mich dein with-Ansatz nochmal auf ein paar unnötige Zeilen bei mir gestoßen. So ist alles wieder etwas kompakter/effizienter geworden.

In diesem Sinne wünsche ich euch ein schönes Restwochenende :-)

Spike S. · 25. August 2015

Das ansprechen des richtigen Worksheets ist immer auch eine Sache, wo das Makro angelegt ist. Liegt es im Tabellenblatt selbst, reicht es wie du es am Anfang hattest, einfach "cells(r,c)". Ist das Makro in einem Modul angelegt oder du musst über mehrere Tabellenblätter hinweg arbeiten, muss man die auch konkret ansprechen. Meiner Erfahrung nach braucht man immer auch ein Workbook-Objekt, zumindest ist es empfehlenswert.

Ich mache es wie GMG-CC, wenn ich mehrfach auf ein Tabellenblatt zugreifen muss, packe ich mir das in eine Variable. Da sieht man auch besser, was los ist, wenn es zu Fehlern kommt. Schreibt man in VBA die Code-Zeilen zu kompakt, ist es manchmal schwer auseinanderzuhalten was in der entsprechenden Zeile die Probleme macht.

Code:

Dim wsWork As Worksheet
Set wsWork = ThisWorkbook.Worksheets("BLATTNAME")

Oder nummerisch über den Index

Code:

Dim wsWork As Worksheet
Set wsWork = ThisWorkbook.Sheets(1)

Wenn nur 1 Tabellenblatt in der Mappe vorhanden ist, kann man auch das "aktive" Tabellenblatt ansprechen (das aktive ist immer das, was sich der Nutzer gerade anschaut).

Code:

Dim wsWork As Worksheet
Set wsWork = ThisWorkbook.ActiveSheet

Der "BLATTNAME" muss natürlich vorhanden sein, wenn der nicht mehr passt kommt es zu Laufzeitfehlern. Das muss der Makroschreiber selber prüfen

Ich muss aber auch manchmal ne Weile grübeln, warum an einer Stelle das Tabellenblatt-Objekt da ist und ansprechbar, an einer anderen Stelle aber nicht. VBA ist da manchmal schwer zu durchschauen...

Ergänzung (25. August 2015)

Ach, und noch ein allgemeiner Tipp: beim Zugriff auf den Inhalt von "cells(r,c)" immer .Value oder .text mitverwenden. Wenn man nicht auf das richtige Value-Attribut zugreift, kann es sonst mal vorkommen, das nicht der Wert zurückkommt den man erwartet.

Code:

Dim wsWork As Worksheet, val as Variant
Set wsWork = ThisWorkbook.Worksheets("BLATTNAME")

val = wsWork.cells(1,1).Value 'liefert den internen Wert der Zelle'
'ODER'
val = wsWork.cells(1,1).text 'liefert den angezeigten Wert der Zelle'

_killy_ · 25. August 2015

Kennst Du zufällig eine gute Tutorial Seite die das Thema bissl ausführlicher behandelt?

Es gibt ein Excel-VBA Wiki -> für den ersten Anlauf immer ganz nett.

Suche

VBA Excel - Saplte um Wert ergänzen wenn in anderer Spalte nicht vorhanden

Phantro

Lt. Junior Grade

Spike S.

Lt. Commander

Phantro

Lt. Junior Grade

Spike S.

Lt. Commander

Phantro

Lt. Junior Grade

Spike S.

Lt. Commander

Phantro

Lt. Junior Grade

Spike S.

Lt. Commander

Phantro

Lt. Junior Grade

cumulonimbus8

Fleet Admiral

Phantro

Lt. Junior Grade

GMG-CC

Gast

Phantro

Lt. Junior Grade

Spike S.

Lt. Commander

_killy_

Commodore

Ähnliche Themen