Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

VBA "sverweis" aber ohne 0 Ergebnisse

Betrifft: VBA "sverweis" aber ohne 0 Ergebnisse von: Jasmin Amato
Geschrieben am: 27.10.2014 17:26:41

Hallo zusammen,

ich habe da ein Problemchen. Ich habe mich in VBA eingelesen ("Excel VBA für Dummies") und mir schon ein Makro geschrieben, das meine Daten so umwandelt wie ich sie gerne haben möchte, aber jetzt komme ich nicht weiter.

Ich habe folgende Datenbasis:

In Spalte B steht der Mitarbeitername. Die Spalte H bis AI repräsentieren einen Zeitraum von 4 Wochen und darin jeweils einen Kalendertag (=Überschrift), in dem deklariert wird an welchem Projekt der Mitarbeiter arbeitet, wobei jeder Eintrag 2 Stunden symbolisiert. In etwa so:

Spalte B Spalte H Spalte I Spalte ...
Mitarbeiter 22.10. 23.10. ...
Max Mustermann Projekt a Projekt a ...
Max Mustermann Projekt a ...

Und jetzt zu meinem Problem. Die Felder sind wie im Beispiel auch zu sehen nicht immer ausgefüllt. Teilweise habe ich eine ganze Zeile mit aber nur einem Pojekt-Eintrag, z.B. am 31.10. Und teilweise auch bis zu 10-15 Zeilen pro Mitarbeiter.

Pro Kalendertag kann ein Mitarbeiter aber maximal 4 Projekte betreuen (=8 Stunden Arbeitszeit).

Was hätte ich nun gerne?
Eine zweite Tabelle in der es pro Mitarbeiter 4 Zeilen gibt und in der in den Spalten H bis AI jeweils die 4 Projekte stehen, die es an diesem Kalendertag gibt. Mit Sverweis komme ich nicht auf das Ergebnis. Mit dem sog. Sverweis2 komme ich bisher auch nicht auf das Ergebnis, weil dieser die leeren Zellen auch als Ergebnis der Sverweis-Suche auch ausgibt. Ich suche also eine Art Sverweis2, der aber das Ergebnis 0 immer überspringt und mir nur "richtige" Ergebnisse wiedergibt.

Gerne etwas, das ich in meinen VBA-Code mit einbauen kann.

Ich hoffe, das war verständlich genug. Falls nötig, lade ich noch eine Beispiel Excel-Tabelle hoch.

Viele Grüße,
Jasmin

  

Betrifft: AW: VBA "sverweis" aber ohne 0 Ergebnisse von: yummi
Geschrieben am: 28.10.2014 10:01:41

Hallo Jasmin,

mal so als Idee: verknüpfe deine Sverweis Formel mit einer Wenn Formel und wenn dir SVerweis eine 0 liefert dann gib halt nix aus sonst das Ergebnis aus Sverweis. Das geht allerdings so auch ohne vba.
Wenn dU es unbedingt mit vba machen willst:

Frage mit if dein Ergenis von SVerweis ab, wenn es nicht dem gewünschten entspricht, weise der Zelle den Wert zu, den du dastellen willst (z.B. Value = "") sonst weise deienr Zelle das Ergebnis von Sverweis zu.

Gruß
yummi


  

Betrifft: AW: VBA "sverweis" aber ohne 0 Ergebnisse von: Jasmin Amato
Geschrieben am: 28.10.2014 10:05:32

Hallo Yummi,

ich habe mich vielleicht nicht deutlich ausgedrückt. Ich möchte, dass wenn das erste ergebnis eine 0 ist, ich in die Zelle nicht "" eintrage, sondern dass die Formel / der Code dann eben die 0 überspringt und so lange sucht, bis es ein Ergebis <>0 gibt und mir dieses dann ausgibt. Da ich Pro Mitarbeiter ja 4 Ergebnisse habe, sollte dann in der darunterliegenden Zelle das nächste ergebnis angezeigt werden (zwischen Ergebnis 1 und 2 können weitere 0-Ergebnisse liegen...).

Macht das mein Problem klarer?


  

Betrifft: Hier eine Beispiel-Datei von: Jasmin Amato
Geschrieben am: 28.10.2014 10:15:58


hier eine Datei: https://www.herber.de/bbs/user/93410.xlsx

Details_MA zeigt die Datenbasis: viele Zeilen Pro Mitarbeiter

Details_MA2 zeigt das, was ich gerne hätte: 4 Zeilen pro Mitarbeiter


  

Betrifft: AW: Hier eine Beispiel-Datei von: yummi
Geschrieben am: 28.10.2014 10:28:30

Hallo Jasmin,

2 Fragen noch:
1.) Deine Mitarbeiter heissen ja alle Max Mustermann oder soll das Max Mustermann1 Max Mustermann2 bis 4 sein und dann wieder 1 - 4?
2.) du hast geschrieben was du in deihnen Code einbauen willst. Wo ist denn dein code?

Gruß
yummi


  

Betrifft: AW: Hier eine Beispiel-Datei von: Jasmin Amato
Geschrieben am: 28.10.2014 10:37:49

Hallo yummi

nein, Max Mustermann ist nur EIN Mitarbeiter. Und das sind die Daten zu genau diesem einen Mitarbeiter. Die Ergebnistabelle soll 4 Zeilen pro Mitarbeiter haben, die auch schon vorher "hart" eingetragen sind (also 4x Max Mustermann dann 4 x Lieschen Müller usw...)
Die anderen Mitarbeiter bzw. anderen Zeilen mit den Projekten aus der Ursprungsdatei hatte ich jetzt für dieses Forum mal rausgelöscht.

Der Code, den ich bisher habe, erzeugt mir das Arbeitsblatt "Details_MA". Nur nun komme ich nicht weiter, also tut der bei diesem Problem nichts zur Sache.

Danke schon mal
Jasmin


  

Betrifft: AW: Hier eine Beispiel-Datei von: yummi
Geschrieben am: 28.10.2014 10:51:33

Hallo Jasmin,

irgendwie versteh ich immer noch nicht ganz wie du zusammen fassen willst.
Wenn die beiden einträge Urlaub vom 24.10 in Zeile 18 und 19 stehen würden, hätte ich gesagt du willst immer jede 4. Zeile zusammen fassen aber so komm ich noch nicht ganz hinter.

Gruß
yummi


  

Betrifft: AW: Hier eine Beispiel-Datei von: Jasmin Amato
Geschrieben am: 28.10.2014 10:54:05

Hallo Yummi,

ich habe pro Tag 4 Einträge, die "irgendwo" stehen können. Ich möchte diese 4 Einträge finden ( soz. mit Sverweis) und untereinanderweg hinschreiben, egal, wo der Eintrag vorher stand. Leider sind es nicht immer 4er Blöcke, wie an dem einen Tag, wo der Kollege nen halben Tag Urlaub hat.

Hilft das?


  

Betrifft: AW: Hier eine Beispiel-Datei von: yummi
Geschrieben am: 28.10.2014 11:40:00

Hallo Jasmin,

das mit dem irgendwo ist ja ok, nur nach welchem Kriterium richtet sich das? Da bei dir alles gleich heisst ist mir nicht klar welcher Max Mustermann (auch wenn es alle die gleichen sind) wann welches Projekt a und welches Projekt b und welchen Urlaub zugewiesen bekommt.

Ich würd dir gern helfen, aber ich verstehe nicht nach welchen Kriterien du zusammen fassen willst.
Schreib doch mal auf was du Schritt für Schritt machst (wonach du suchst) wenn du händisch etwas zusammen fasst.

Gruß
yummi


  

Betrifft: AW: Hier eine Beispiel-Datei von: Jasmin Amato
Geschrieben am: 28.10.2014 11:55:42

Hallo yummi,

ich habe jetzt nochmal 2 Personen eingefügt. Wird es jetzt klarer, was ich möchte?

https://www.herber.de/bbs/user/93413.xlsx

Vllt. ist mein Problem ja auch unlösbar und ich muss komplett umdenken...

Danke!
Jasmin


  

Betrifft: AW: Hier eine Beispiel-Datei von: yummi
Geschrieben am: 28.10.2014 12:16:27

Hallo Jasmin,

ich versuch mal pseudo code, sag mal ob das passt:

Durchlaufe Spalte B
Wenn <> Leer
erzeuge Name in MA2
übernehme nicht leere Einträge dieser Zeile
wenn Zelle in MA2 schon gefüllt
erzeuge neue Zeile mit selben Mitarbeiter in MA 2
übernehme die nicht leeren Daten

so in etwa? Sind noch nicht alle Fälle berücksichtigt. so müsste man in MA2 alle Zeilen nach dem selben Namen durchsuchen und prüfen ob die gewünschte Zelle leer ist sonst weitere Zeile mit selben Mitarbeiter suchen und prüfen bis keine Zeile mehr gefunden und dann neue Zeile mit dem Mitarbeiter einfügen.

Sag mal ob das dem entspricht was Du willst

Gruß
yummi


  

Betrifft: AW: Hier eine Beispiel-Datei von: Jasmin Amato
Geschrieben am: 28.10.2014 12:27:44

Hallo Yummie,

Spalte B im Blatt Details_MA2 ist bereits ausgefüllt, da ich ja weiß, welche Mitarbeiter ich habe und dass jeder 4 Einträge pro Tag hat. Spalten H bis AI müssen ausgefüllt werden.

ich stelle mir das so vor:

Aktiviere Details_MA2.
Gehe in Zelle H8 (also 1. Tag beim 1. Mitarbeiter) und suche für den 1. Mitarbeiter (=Max Mustermann) im Blatt Details_MA in der Spalte H den ersten Eintrag und schreib in ihn Zelle H8. Dann gehe in Zelle H9 suche wieder im Blatt Details_MA in Spalte H und schreib den 2. Eintrag in Zelle H9. Dann den 3. und 4. für H10 und H11. Dann sind wir ja beim 2. Mitarbeiter in Zelle H12 und starten mit der suche nach dem 1. Eintrag für Lieschen Müller in Spalte H. usw für Spalte H bis alle Mitarbeiter ihre 4 Einträge haben. Dann weiter mit Spalte I, J, K bis AI.

Formel wird ja nicht gehen. Komme ich da mit der For-Schleife und einer IF Abfrage weiter? Aber wie sage ich dem Makro, dass ich dann in der zweiten Zelle bitte das 2. Ergebnis haben möchte usw...

Grüße,
Jasmin


  

Betrifft: AW: Hier eine Beispiel-Datei von: yummi
Geschrieben am: 28.10.2014 13:34:02

Hallo Jasmin,

schau dir mal das Makro an. Es schreibt momentan in die Kopie deines MA2 Sheets (musst du bei dir zum Testen mal anlegen). Das kannst Du in Initialisiere anpassen. Müsste dem entsprechen, was Du willst (so hoffe ich)

Option Explicit
Dim wkb As Workbook
Dim wksQ As Worksheet
Dim wksZ As Worksheet

Sub DatenSammeln()
Dim i As Long
Dim j As Integer
Dim lletzteZeileQ As Long
Dim lletzteZeileZ As Long
Dim lletzteSpalteQ As Long
Dim lletzteSpalteZ As Long
Dim strRange As String
Dim rng As Range
Dim bEingetragen As Boolean
Dim lreihe As Long
Dim lReiheErste As Long

    Initialisiere
    lletzteZeileQ = wksQ.Cells(Rows.Count, 2).End(xlUp).Row
    lletzteZeileZ = wksZ.Cells(Rows.Count, 2).End(xlUp).Row
    lletzteSpalteQ = wksQ.Cells(7, 256).End(xlToLeft).Column
    lletzteSpalteZ = wksZ.Cells(7, 256).End(xlToLeft).Column
    strRange = "B7:B" & lletzteZeileZ
    For i = 8 To lletzteZeileQ
        Set rng = wksZ.Range(strRange).Find(wksQ.Cells(i, 2).Value)
        If Not rng Is Nothing Then
            lreihe = rng.Row
            lReiheErste = lreihe
            For j = 8 To lletzteSpalteQ
                If wksQ.Cells(i, j).Value <> "" Then
                    lreihe = lReiheErste
                    bEingetragen = False
                    Do While bEingetragen = False
                        If wksZ.Cells(lreihe, j).Value = "" Then
                            bEingetragen = True
                            wksZ.Cells(lreihe, j).Value = wksQ.Cells(i, j).Value
                        Else
                            lreihe = lreihe + 1
                            If wksZ.Cells(lreihe, 2).Value <> wksQ.Cells(i, 2).Value Then
                               MsgBox ("Nicht genug Zeilen in Zielsheet für " & wksQ.Cells(i, 2) _
.Value & " vorhanden")
                               Exit Sub
                            End If
                        End If
                    Loop
                End If
            Next j
        End If
    Next i
    
End Sub

Function Initialisiere()
    Set wkb = ThisWorkbook
    Set wksQ = wkb.Sheets("Details_MA")
    Set wksZ = wkb.Sheets("Details_MA2 (2)")   'hier anpassen
End Function


Gruß
yummi


  

Betrifft: Ich rast aus, das Funktioniert :D von: Jasmin Amato
Geschrieben am: 28.10.2014 14:19:56

Hallo Yummi,

krass, das funktioniert :)
Tatsächlich habe ich bei ein paar Mitarbeitern nicht genügend Zeilen in der Ziel-Tabelle, das ist aber wahrscheinlich ein eher organisatorisches Problem.

Wäre es möglich statt der MsgBox unter dem jeweiligen MA Zeilen einfügen zu lassen, so dass die Daten doch noch aufgelistet werden?

Und, nur falls du dafür Zeit hast. Könntest du mir erklären, was das Makro macht? Ich stehe ja noch ganz am Anfang meiner VBA-Karriere und möchte so viel wie möglich lernen.

DAAAAAANKE!


  

Betrifft: AW: Ich rast aus, das Funktioniert :D von: yummi
Geschrieben am: 28.10.2014 14:42:19

Hallo Jasmin,

ja auch das ist möglich :-)

wksZ.Rows(lreihe & ":" & lreihe).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

anstatt der MsgBox und exit sub

Erklärung versuch ich mal:

In Initialisiere schaffe ich mir Variablen mit denen ich einfach auf die einzelnen Tabellenblätter referenzieren kann. Diese kännte man noch auf nothing setzen, wenn man sie nicht mehr braucht, fällt hier aber aus, da sie solange gelten sollen, wie die Datei offen ist.

Danach besorge ich mir so wie die Variablennamen ja schon sagen die letzte Zeile und Spalte ( da ich sie evtl zum Zeitpunkt des Programmierens nicht kenne) Z für Ziel Q für Quelle

Dann durchlauufe ich alle Zeile von 8 an (for i)

Find sucht im angegebenen Bereich (strRange) nach dem Wert der in der Zeile in Spalte 2 (B) der Quelle steht (wksQ.Cells(i, 2).Value)

Der Rückgabewert ist ein Range (siehe Hilfedatei von excel). Wenn nichts gefudnen wurde ist der Wert nothing, daher die folgende if Abfrage

Dann merke ich mir die Reihe die ich gefunden habe

Jetzt durchlaufe ich Spaltenweise von 8 (H) an bis zur letzten Spalte (for j)

wenn ich in der aktuellen Zeile (i) und der aktuellen Spalte (j) in der quelle einen Wert <> "" finde dann gehts weiter

es wird wieder auf die erste Reihe des gesuchten Namens gesetzt (könnte beim ersten Durchlauf entfallen)

ich benutze bEintragen zum Steuern, also erstmal false (noch nicht eingetragen)

solange noch nicht eingetragen mache folgendes

wenn in der durchsuchten Reihe in der aktuellen spalte kein Wert steht: eintragen und steuervariable für Schleife setzen

sonst Reihe um 1 erhöhen und Abfrage ob immer noch der richtige Name in der neuen Zeile vorhanden ist.

und das für jede Spalte (for j)

und in jeder Zeile (for i)


Geh es mal schritt für Schritt (F8) im VBA Editor durch und lass dir die Variablen anzeigen, dann siehst du was passiert, ist kein Hexenwerk ;-)


Ich hoffe so wird es klarer

Gruß
yummi


  

Betrifft: Ein paar Fragen hab ich noch... von: Jasmin Amato
Geschrieben am: 28.10.2014 15:11:27

Danke für die Erklärung. Damit muss ich mich mal ein bischen länger befassen.

Sehe ich das richtig, dass, wenn nicht genügend Zeilen, da sind, das Makro durch "Exit Sub" an der Stelle einfach aufhört.

Könnte ich das Exit Sub weglassen, stattdessen Zeile einfügen (wie von dir vorgeschlagen), dann vorne in Spalte B und der entsprechenden Zeile den fehlenden Namen schreiben lassen und mit dem Makro weiterlaufen? Evtl. so mit copy paste von der Zelle darüber?

Nächste Frage:

Im Code steht

lletzteSpalte = wksQ.Cells(7, 256) ...

warum 256? Meine Tabelle geht ja bis AI, das wäre 34. Das würde doch auch reichen, oder?


  

Betrifft: AW: Ein paar Fragen hab ich noch... von: yummi
Geschrieben am: 28.10.2014 15:32:20

Hallo Jasmin,

zu 1: ja siehst du richtig
zu 2: ja kannst du so machen:
wksZ.Rows(lreihe & ":" & lreihe).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
wksZ.Cells(lreihe,2).Value = wksQ.Cells(i, 2).Value

fügt dir eine Zeile ein und kopiert in Spalte B den gerade aktuellen Namen nach dem gesucht wird aus der Quellseite.

zu 3: im Prinzip würde für deinen Fall die 34 reichen, aber nur solange du keine weiteren spalten einfügst. 256 ist die maximale Anzahl an Spalten die es gibt. Die 7 davor bedeutet, dass die letzteSpalte in Zeile 7 gesucht wird.


Gruß
yummi


  

Betrifft: Hoffentlich letzte Frage... von: Jasmin Amato
Geschrieben am: 28.10.2014 16:08:45

Hallo Yummi,

danke dass du mir schon so gut geholfen hast.

Meine hoffentlich letzte Frage:

Ich möchte deinen Code in meinen bestehenden Code einbauen. Muss ich nun bevor Initialisiere durchlaufen kann das Blatt Details_MA2 (2) aktivieren lassen? Oder erübrigt sich das durch den Function-Teil unten?

Viele Grüße,
Jasmin


  

Betrifft: AW: Hoffentlich letzte Frage... von: yummi
Geschrieben am: 28.10.2014 16:35:41

Hallo Jasmin,

du musst dein Tabellenblatt nicht "Details_MA2 (2)" nennen. Du kannst dein altes nehmen, musst dann in Initialisere nur den korrekten Namen eingeben.

Die function Initialisiere belegt die Variablen wksQ und wksZ mit Inhalt, also nach Initialisiere verweisen die beiden Variablen auf die Tabellenblätter mit den dort angegebenen Namen. Ich habe die Referenzierung der Zellen überall komplett gemacht. Es ist also egal welches Tabellenblatt aktiv ist, du könntest sogar noch neue Tabellenblätter machen, auch das würde nicht stören :-). Sie werden nur so noch nicht vom Code angesprochen.

Es gibt 2 Dinge die du vor Start des Makros beachten musst:

1. Die Arbeitsmappe muss aktiv sein.
2. Die Tabellenblätter, die du in Initialisiere angibst, müssen in der Arbeitsmappe vorhanden sein (sonst musst du in Initialisiere den richtigen Namen der Blätter angeben).

Das ganze arbeitet ohne Activate oder select, dadurch kein Flackern. Wenn Du es noch "schöner" haben willst kannst Du vor Start noch Application.ScreenUpdating = false und am danach wieder auf True setzen.

Gruß
yummi


  

Betrifft: AW: Ich rast aus, das Funktioniert :D von: Jasmin Amato
Geschrieben am: 28.10.2014 17:00:20

Hallo Yummi,

ich arbeite mich eben durch den Code.

Den Teil verstehe ich nicht, auch nicht mit deiner Erklärung:

lreihe = rng.Row
lReiheErste = lreihe

Wozu brauchst du die Variablen lreihe und lReiheErste? Du sagst damit doch, dass die Variable mit dem Datentyp Long (also eine ganze Zahl, richtig?) den Wert der Zeile von rng annehmen soll. Und lReiheErste soll gleich lreihe sein, also auch den Wert der Zeile von rng annehmen soll.

Weiter geht es dann mit:
For j = 8 To lletzteSpalteQ
    If wksQ.Cells(i, j).Value <> "" Then
        lreihe = lReiheErste
        bEingetragen = False
D.h. wenn das If zutrifft, also der Wert der Zelle leer ist, dann soll lreihe gleich lReiheErste sein. Aber vorher hattest du das ja schon andersrum deklariert. Irgendwie dreht sich das doch im Kreis oder?

Oder ist das Sinn des Ganzem? Weil du im 2. Schritt eben nicht mehr sagen kannst lreihe = rng.Row, nimmst du den Umweg über die Variable lReiheErste?

Danke + Grüße,
Jasmin


  

Betrifft: AW: Ich rast aus, das Funktioniert :D von: yummi
Geschrieben am: 28.10.2014 19:55:36

Hallo Jasmin,

richtig ich merke mir in lreihe und in lReiheErste die erste Reihe die den gesuchten Namen enthält.
wenn ich aber den einzutragenden Wert nicht in dieser Reihe eintragen kann, dann erhöhe ich lReihe solange bis ich eintragen kann. Danach setzte ich den Wert wieder auf lErsteReihe zurück, damit ich mit dem nächsten einzutragenden Wert wieder von Anfang an überprüfen kann, wo die freie Zelle ist.

Ich hoffe es ist klarer geworden, schau dir das mal im Debugger an. Dann erkennst du was passiert.

Gruß
yummi


  

Betrifft: Alles verstanden! Kann geschlossen werden von: Jasmin Amato
Geschrieben am: 29.10.2014 11:29:54

Hallo Yummi,

ich hab mir das jetzt aufgemalt und ich habe den Code verstanden :) Juhu! Echt raffiniert, was du da geschrieben hast. Ich hoffe, ich kann sowas bald auch allein.

Nur der Umweg über

lreihe = rgn.Row
lReiheErste = lreihe
'und später dann
lreihe = lReiheErste

ist mir weiterhin ein Rätsel. Aber so lange es funktioniert...

Vielen vielen Dank für den schnellen Support!

Viele Grüße,
Jasmin


  

Betrifft: AW: Alles verstanden! Kann geschlossen werden von: yummi
Geschrieben am: 29.10.2014 12:43:33

Hallo Jasmin,

die ersten beiden Zeilen sind zum initialisieren der Variablen da.
die Zeile später setzt lreihe nur wieder auf den Anfangswert zurück für den nächsten Spalteneintrag. Diese Zeile könnte beim 1. mal auch entfallen. ist aber komplizierter sie entfallen zu lassen, als einaml zuviel auszuführen.

das ganze optimiert könnte so aussehen:

lReiheErste = rng.Row
rng.Row = nothing 'rng wird nicht mehr benötigt

und später in der for Schleife
lReihe = lReiheErste


macht dasselbe, vlt wird es so klarer.


Programmieren lernt man durch doing, wenn du also viel programmierst, dann wirst Du es bestimmt auch schaffen, solche Probleme selbst zu lösen. Einfach üben :-) z.B. mit den Fragen hier aus dem Forum (und danach kannst Du deine Lösung mit den hier vorgeschlagenen vergleichen)

Viel Spass dabei

yummi


  

Betrifft: AW: Alles verstanden! Kann geschlossen werden von: Jasmin Amato
Geschrieben am: 29.10.2014 14:03:40

ah... weil lReihe ja, falls bEingetragen = true, +1 gerechnet wird. und dann setzt du es wieder zurück. ich verstehe.


  

Betrifft: AW: Alles verstanden! Kann geschlossen werden von: Jasmin Amato
Geschrieben am: 30.10.2014 10:31:40

Hallo Yummi,

eine Sache ist mir eben aufgefallen. Wie kann ich das eleganter lösen:

Ich hatte ja, statt der MsgBox "Zu wenig Zeilen" neue Zeilen eingefügt. Dadurch ändert sich ja die letzte Zeile (lletzeZeileZ) und das Makro durchläuft nicht mehr alle Zeilen. Also die letzten Zeilen werden nicht mehr ausgefüllt.
Das liegt daran:

strRange = "B7:B" & lletzteZeileZ
Kann es sein, dass lletzteZeileZ auf die letzte Zeile VOR einfügen der Zeilen festgelegt wird? Wie kann ich das dynamisch gestalten, so dass auch beim Durchlaufen der letzten Schleife alle Zeilen gepürft und in meinem Fall mit Daten befüllt werden.

Ich habe das jetzt mal so gelöst:
strRange = "B7:B500"
Da ich bei meinen aktuellen Daten weiß, dass ich weniger als 500 Zeilen brauche. Aber elegant und "safe" ist das auch nicht, sollten sich die Daten mal erhöhen.

Danke!

Viele Grüße,
Jasmin

Hier nochmal der ganze Code:

Option Explicit
    Dim wkb As Workbook
    Dim wksQ As Worksheet
    Dim wksZ As Worksheet
  
    
Sub MAProjektübersicht()

Dim i As Long
Dim j As Integer


Dim lletzteZeileQ As Long
Dim lletzteZeileZ As Long
Dim lletzteSpalteQ As Long
Dim lletzteSpalteZ As Long
'Q steht für Quelldaten, Z für Zieldaten

Dim strRange As String
Dim rng As Range
Dim bEingetragen As Boolean
Dim lreihe As Long
Dim lReiheErste As Long



    Initialisiere
    lletzteZeileQ = wksQ.Cells(Rows.Count, 2).End(xlUp).Row
    lletzteZeileZ = wksZ.Cells(Rows.Count, 2).End(xlUp).Row
    lletzteSpalteQ = wksQ.Cells(7, 256).End(xlToLeft).Column
    lletzteSpalteZ = wksZ.Cells(7, 256).End(xlToLeft).Column
'    strRange = "B7:B" & lletzteZeileZ 
    strRange = "B7:B500"
    wksZ.Activate
    For i = 8 To lletzteZeileQ
        Set rng = wksZ.Range(strRange).Find(wksQ.Cells(i, 2).Value)
        If Not rng Is Nothing Then 
            lreihe = rng.Row
            lReiheErste = lreihe
            For j = 8 To lletzteSpalteQ
                If wksQ.Cells(i, j).Value <> "" Then
                    lreihe = lReiheErste
                    bEingetragen = False
                    Do While bEingetragen = False
                        If wksZ.Cells(lreihe, j).Value = "" Then
                            bEingetragen = True
                            wksZ.Cells(lreihe, j).Value = wksQ.Cells(i, j).Value
                        Else
                            lreihe = lreihe + 1                   
                            If wksZ.Cells(lreihe, 2).Value <> wksQ.Cells(i, 2).Value Then
                            wksZ.Rows(lreihe & ":" & lreihe).Insert Shift:=xlDown, CopyOrigin:= _
xlFormatFromLeftOrAbove
                            wksZ.Cells(lreihe, 2).Value = wksQ.Cells(i, 2).Value
                            wksZ.Cells(lreihe, 2).Interior.Color = 255
                            End If
                        End If
                    Loop
                End If
            Next j
        End If
    Next i

wksZ.Range("H8:AI1048576").WrapText = True
wksZ.Rows("8:1048576").RowHeight = 12
    
End Sub

Function Initialisiere()
    Set wkb = ThisWorkbook
    Set wksQ = wkb.Sheets("Details_MA")
    Set wksZ = wkb.Sheets("Projektübersicht")   'hier anpassen
End Function



  

Betrifft: AW: Alles verstanden! Kann geschlossen werden von: yummi
Geschrieben am: 31.10.2014 09:41:38

Hallo Jasmin,

das ist nicht schwer ;-)
lletzteZeileZ = wksZ.Cells(Rows.Count, 2).End(xlUp).Row
berechnet die letzte benutze Zeile auf deinem Zielsheet. Wie Du schon gesagt hast passiert das ja nur einmal und wenn du eine Zeile einfügst passt der Wert nicht mehr.

Was müsste denn mit dem WErt passieren, wenn du eien Zeile einfügst? ..... richtig der Wert müsste sich um einen erhöhen und zwar nur wenn du eien Zeile einfügst. Mit diesen Überlegungen kommt nur eine Lösung in Frage:

In diesem Teil müssen wir den Wert erhöhen

                           If wksZ.Cells(lreihe, 2).Value <> wksQ.Cells(i, 2).Value Then
                            wksZ.Rows(lreihe & ":" & lreihe).Insert Shift:=xlDown, CopyOrigin:=  _
_
xlFormatFromLeftOrAbove
                            wksZ.Cells(lreihe, 2).Value = wksQ.Cells(i, 2).Value
                            wksZ.Cells(lreihe, 2).Interior.Color = 255
                            lletzteZeileZ  = lletzteZeileZ  + 1
                            End If
und schon läuft die Schleife wieder bis zum Ende. Die Grenzen in der Schleife erhöhen ist noch legitim, was man nicht machen sollte (sehr schlechter Stil) ist die Laufvariable in eienr SChleife zu manipulieren.

Du warst auf dem richtigen Weg, hast es nur nicht zu Ende gedacht ;-)

Gruß
yummi


 

Beiträge aus den Excel-Beispielen zum Thema "VBA "sverweis" aber ohne 0 Ergebnisse"