VBA Wenn kein Treffer, dann mit nächster Tabelle weitermachen

[Stoffo]

Nachtrag:

Moin,

nun tut sich leider ein neues Problem auf. Soweit werden alle Daten richtig kopiert, jedoch gibt es (ausschließlich) im ersten Durchgang

Code:

    sheetNames4 = Array("Tabelle4")
    sheetSearchColumns4 = Array(3)
    Set wkb = ThisWorkbook
    For i = LBound(sheetNames4) To UBound(sheetNames4)
        Set tmp4 = GetRowsWithMultiColumnMatch(wkb.Worksheets(sheetNames4(i)).Cells(1, 1).CurrentRegion, sheetSearchColumns4(i), "Suchbegriff")
        If Not tmp4 Is Nothing Then tmp4.Copy Workbooks.Open("Mappe2.xlsm").Sheets("hier sollen die daten rein").Cells(7, 1).End(xlUp).Offset(1, 0)
    Next i

ein Problem beim Einfügen. Ich gebe zwar .Cells(7, 1)=A7 an, die Daten werden jedoch in A6 eingefügt. Wenn ich .Cells(6,1) eingebe landen die Daten ebenfalls in A6 und bei .Cells(8,1) richtigerweise in A8. Hat jemand eine Idee woran es liegen könnte und kann mir die Behebung des Fehlers erklären? Gibt es darüber hinaus eine Möglichkeit lediglich die Werte einzufügen statt der Quellformatierung? Danke und

[derHoepp]

Hallo und guten Morgen,

das Problem liegt wahrscheinlich daran, dass du die Funktion der .End-Methode und der .Offset()-Methode noch nicht ganz verstanden hast. Vielleicht schaust du mal in die Online-Hilfe, dann kommst du sicherlich von selbst drauf.

Viele Grüße
derHöpp

[Stoffo]

Moin,

Danke für die Rückmeldung. In der Tat ist es so, dass ich mich mit .End und .Offset bis dato noch nicht auseinander gesetzt habe. Nach kurzem einlesen verstehe ich das Problem dennoch leider nicht, wenngleich ich herausgefunden habe, dass Offset(2,0) das Problem löst. So wie ich es verstehe wird durch

Code:

        If Not tmp4 Is Nothing Then tmp4.Copy Workbooks.Open("Mappe2.xlsm").Sheets("hier sollen die daten rein").Cells(7, 1).End(xlUp).Offset(1, 0)

zunächst geprüft ob tmp4 nicht leer ist. Falls dies der Fall ist, werden die Daten mittels .copy in meiner Zielmappe 2 im Arbeitsblatt "hier sollen die Daten rein" eingefügt und zwar an der Stelle .Cells(7,1) = A7

.End(xlUp) regelt (meinem Verständnis nach) das Alle Daten bis zum Zeilenende eingefügt werden und zwar in der oberen Zelle meines Angegebenen Bereichs (.Cells (7,1)), also A6?
.Offset(1,0) da positive Zahlen müsste dann die einzufügenden Daten um eine Zeile nach unten verschieben (von A6 zu A7???) , dies steht allerdings im Wiederspruch zum aktuellen Fall da die Daten stattdessen in A6 landen und bei allen anderen Tabellen(tmp1 bis 3) die Daten an den angegebenen .Cells Bereichen landen. Ich vermute daher eher, dass etwas am Array verkehrt ist, da dies das einzige mit lediglich einer zu prüfenden Spalte darstellt.
Wäre schön, wenn du mir noch einen Tritt in die richtige Richtung geben könntest bzw. meine Einschätzung auf Richtigkeit überprüfst.

[HKindler]

Hi,

kennst du die Hilfe-Funktion im VBA-Editor?
Teste mal folgendes: stelle den Cursor auf oder direkt hinter das End von ...Cells(7, 1).End(xlUp).Offset... Damit landest du automatisch auf der entsprechenden Hilfe-Seite von MS. Dort kanns du dann folgendes lesen: 

Gibt ein Range-Objekt zurück, das die Zelle am Ende des Bereichs repräsentiert, die den Quellbereich enthält. Äquivalent zum Drücken von ENDE-TASTE+NACH-OBEN-TASTE, ENDE-TASTE+NACH-UNTEN-TASTE, ENDE-TASTE+NACH-LINKS-TASTE oder ENDE-TASTE+NACH-RECHTS-TASTE.

Du kannst also einfach mit der Tastatur testen, wie .End(xlUp) reagiert.

[Stoffo]

Moin,

auch diese Funktion kannte ich nicht, funktioniert jedoch auch nicht wie von dir beschrieben. Wenn ich den Cursor auf das End stelle und dann ? -> Microsoft Visual Basic for Applications-Hilfe auswähle öffnet sich folgende Seite:

Link

Wenn ich dann auf der linken Seite nach .End Suche erhalte ich 333.466 Ergebnisse für ".end"

Wenn ich dann ebenfalls auf der linken Seite bei Produkten nur Excel anhake sind es immernoch 7.731 Ergebnisse für ".end"

Das von dir erwähnte Zitat habe ich nicht finden können. Muss ich ggf. iwo in meinem VBA-Editor zusätzliche Einstellungen vornehmen um auf die von dir erwähnte Seite geleitet zu werden oder bediene ich den von mir angeführten Link einfach nur falsch?

EDIT: Mittels Googlesuche nach .End bin ich auf der von dir erwähnten Seite gelandet (und habe mir auch direkt Offset dort nochmal angeschaut), ich würde dennoch gerne wissen wie ich aus dem Editor heraus dorthin gelangen kann. Habe nun auch Dank der von dir zitierten Tastenkombi herausgefunden dass ich in A1 lande, hilft mir beim Verständnis der beiden Methoden jedoch leider trotzdem nicht. Die Daten von tmp werden von A1 eine Zeile nach unten gesetzt? Wofür gebe ich A7 dann überhaupt an? Und Warum landen die Daten in A6? Wäre hierfür nicht zumindest Offset(-1,0) nötig oder bei weglassen von cells ein offset von 6,0? Habe aktuell viele Fragezeichen

EDIT2:
Oder bedeutet .End und .Offset dass von den Quelldaten(=Mappe1) die 1.Zeile (=Überschriften) weggelassen werden? Wozu ist dies nötig, wenn in den Überschriften der Suchtext nicht enthalten ist? Dann dürfte theoretisch doch gar kein Wert zurück geliefert/ in der Variable gespeichert werden, oder?
Und selbst dann würde dies nicht erklären, wieso ich bei Angabe von .Cells(7,1) die Daten in A6 eingefügt bekomme.

[HKindler]

Oh Mann, sorry, vor lauter Formatieren etc. habe ich das wichtigste vergessen. Es sollte heißen: Stell den Cursor auf das End und drücke dann F1. 
Tut mir leid für die Verwirrung. Nochmals Sorry!

Und wieso du Cells(7,1).end().offset() verwendest anstatt wie vom Hoepp vorgeschlagen Cells(rows.count,1).end().offset() wirst wohl nur du wissen.

[Stoffo]

Moin,

mit F1 klappt es nun. Danke für den Hinweis.
Ich habe das Makro von derHoepp an diversen Stellen angepasst, da in seinem Code das Makro in Mappe2 ausgeführt wird, ich es jedoch von Mappe 1 ausführen möchte. Darüber hinaus hat sein Makro mir im Ergebnis alle Fundstellen aller Tabellen untereinander geliefert, während ich die Fundstellen einzelner Tabellen an bestimmten Stellen brauche
Funde aus Tabelle 1 sollen untereinander ab A11 eingetragen werden
Funde aus Tabelle 2 sollen untereinander ab A39 eingetragen werden
Funde aus Tabelle 3 sollen untereinander ab A47 eingetragen werden
und Funde aus Tabelle 4 sollen ab A7 eingetragen werden

Das Makro von derHoepp lieferte (nach Hinterlegung von "hier sollen die daten rein") jedoch alle Ergebnisse untereinander, welche in Mappe 2 ab A48 eingetragen wurden (vermutlich wegen .end und .offset?). Um mein Ziel zu bewerkstelligen habe ich daher ein wenig "rumgebastelt" und bin eben zu dem Ergebnis von #10 gelangt, welches jedoch zum Problem aus #11 führte welches ich mir bis dato immernoch nicht erklären kann und welches letztlich hierher geführt hat. Daher meine Bitte an die Profis mir den anscheinend für alle anderen offensichtlichen Fehler zu erläutern und darüber hinaus ggf. eine Rückmeldung zu meinen diversen Verständnisfragen zu geben um solche Probleme zukünftig eigenständiger lösen zu können. Danke und

[HKindler]

Hi,

Geh doch mal gedanklich die Codezeile vom Hoepp durch:
Cells(rows.count,1)
Das ist die allerletzte Zeile in A. Also A1.000.000 (bzw. etwas mehr)
.End(xlUp)
Springt jetzt so weit nach oben, bis eine Zelle mit Inhalt kommt. Jetzt bist also auf der letzten benutzten Zeile in Spalte A.
.Offset(1)
Geht eine Zeile runter. Damit bist du auf der ersten leeren Zeile.
Das ist schon das ganze Geheimnis.
Wieso man dann nicht Cells(1,1).End(xlDown) verwendet? Weil man dann bei der ersten leeren Zelle in Spalte A landet. Und sehr oft ist das eben nicht die letzte Zeile der Spalte.

[schauan]

Hallöchen,

1)

ein Problem beim Einfügen. Ich gebe zwar .Cells(7, 1)=A7 an, die Daten werden jedoch in A6 eingefügt. Wenn ich .Cells(6,1) eingebe landen die Daten ebenfalls in A6 und bei .Cells(8,1) richtigerweise in A8.

Das schaut aus, als ob in A5 was steht und A6 leer ist. Kannst Du auch wieder manuell mit den Tastenkombis nachstellen.
Wenn die Funde aber immer ab A7, A11 usw.  eingetragen werden sollen, brauchst Du das xlUp doch gar nicht unbedingt?

2)
Du nimmst ein Array mit den Tabellennamen. Bilde ein zweites Array mit den Startzeilen. also z.B. arrZeilen = Array(7, 11, ...)
Dann kannst Du z.B. statt Cells(7, 1) dann Cells(arrZeilen(i), 1) nehmen.

und nochmal 1)
ich schrieb ja bei 1) das man xlUp nicht unbedingt braucht und bei 2) von Cells(arrZeilen(i), 1)
xlUp wäre generell falsch, weil Du immer von der ersten Eintragungszeile nach oben springst ...

Feile mal bisschen an der Logik / dem Ablauf.

Du könntest vor der Schleife den Bereich ab A7 leeren
In der Schleife prüfst Du, ob die erste Zelle einen Eintrag erhält. Davon abhängig - also, wenn nichts da steht, setzt Du einen Zeilenzähler auf den Anfangswert des Bereichs.
Am Ende der Schleife setzt Du dann den Zeilenzähler 1 hoch - und brauchst dadurch auch den Offset nicht.

Also im Prinzip z.B.

Code:

For i = .. to ..
  if Cells(arrZeilen(i), 1).Value = "" then k = arrZeilen(i)
  ...
  ... .Sheets("hier sollen die daten rein").Cells(k, 1) 'ohne xlUp und Offset
  ...
  k=k+1
Next

Warum machst Du die Zieldatei eigentlich in der Schleife auf und nicht davor?

[Stoffo]

Hallo ihr Zwei,

zunächst Danke für eure Bemühungen. Mir wird langsam einiges klarer 

@HKindler:
Mir war bis dato nicht bewusst, dass ALLE Zeilen gezählt werden. Ich bin die ganze Zeit davon ausgegangen, dass lediglich die Zeilen mit Inhalt gezählt werden. Auch dein Beispiel bzgl.

Wieso man dann nicht Cells(1,1).End(xlDown) verwendet? Weil man dann bei der ersten leeren Zelle in Spalte A landet. Und sehr oft ist das eben nicht die letzte Zeile der Spalte.

ist sehr anschaulich und absolut logisch. Wir scheinen jedoch ein wenig aneinander vorbei geschrieben zu haben, denn so wie @schauan es Eingangs beschreibt:

ein Problem beim Einfügen. Ich gebe zwar .Cells(7, 1)=A7 an, die Daten werden jedoch in A6 eingefügt. Wenn ich .Cells(6,1) eingebe landen die Daten ebenfalls in A6 und bei .Cells(8,1) richtigerweise in A8.

Das schaut aus, als ob in A5 was steht und A6 leer ist.

ist es bei mir eben nicht. Ich habe in A6 Überschriften, also Text stehen und obwohl ich Cells(7,1).End(xlUp).Offset(1,0) verwendet habe wurden die gefundenen Daten kontinuierlich in A6 eingetragen. Nachdem wie ich es nun verstanden habe hätten gemäß genannter Formel die Daten aber von A7 (zu diesem Zeitpunkt leer) ausgehend, durch End(xlUp) A6 auswählend und durch Offset auf A7 landen müssen. Ich weiss nicht ob es sich hierbei um einen Bug des Codes handelt, ich gehe jedoch davon aus da die darauf folgenden Funde alle richtig eingetragen wurden und der Code gemäß #10 bei allen Ausführungen identisch ist und immer in der Zeile über der angegebenen .Cells(x,y) eine Überschriftenzeile befindlich ist. Ist nun aber auch hinfällig, da ich den Code durch eure Anmerkungen nochmal angepasst habe.


@schauan:
Auch dir vielen Dank für die sehr ausführliche Antwort. Ich habe mich nun nochmal rangesetzt und hoffentlich alles gut umsetzen können. Auf das leeren der Bereiche ab A7,A11... habe ich verzichtet, da diese Bereiche zu Beginn eh immer leer sind.

Das aktuelle funktionierende Ergebnis sieht wie folgt aus, ich hoffe ihr seid mit dem Ergebnis ebenso zufrieden wie ich, anderenfalls bin ich für weitere Verbesserungen offen:

Code:

Option Explicit
Sub test2()
    Dim wkb As Workbook
    Dim wkbtarget As Workbook
    Dim sheetNames As Variant
    Dim sheetTargets As Variant
    Dim sheetSearchColumns As Variant
    Dim tmp As Range
    Dim i As Long

    sheetNames = Array("Tabelle4", "Tabelle1", "Tabelle2", "Tabelle3")
    sheetSearchColumns = Array(3, Array(27, 28, 29, 30, 31), Array(37, 38, 39, 40, 41), Array(54, 55, 56, 57, 58))
    sheetTargets = Array(7, 11, 39, 47)
    Set wkb = ThisWorkbook
    Set wkbtarget = Workbooks.Open("Pfad\Mappe2.xlsm")
    For i = LBound(sheetNames) To UBound(sheetNames)
        Set tmp = GetRowsWithMultiColumnMatch(wkb.Worksheets(sheetNames(i)).Cells(1, 1).CurrentRegion, sheetSearchColumns(i), "Suchbegriff")
        If Not tmp Is Nothing Then tmp.Copy wkbtarget.Sheets("hier sollen die daten rein").Cells(sheetTargets(i), 1)
    Next i
End Sub

Function GetRowsWithMultiColumnMatch(SourceRange As Range, ColumnNumbers As Variant, SearchTerm As String) As Range

    Dim tmpRange As Range
    Dim i As Long
    Dim SearchRange As Range
    Dim fnd As Range
    Dim firstaddress As String
   
    'Einzelspalte in Array wandeln
    If Not IsArray(ColumnNumbers) Then
        i = ColumnNumbers
        ReDim ColumnNumbers(0)
        ColumnNumbers(0) = i
    End If
       
    'Alle angegebenen Spalten des Quellbereichs zu einem Suchbereich vereinen
    For i = LBound(ColumnNumbers) To UBound(ColumnNumbers)
        If SearchRange Is Nothing Then
            Set SearchRange = SourceRange.Columns(ColumnNumbers(i))
        Else
            Set SearchRange = Union(SearchRange, SourceRange.Columns(ColumnNumbers(i)))
        End If
    Next i
   
    'Innerhalb des festgelegten Suchbereichs suchen...
    Set fnd = SearchRange.Find(what:=SearchTerm, LookIn:=xlValues, lookat:=xlWhole)
   
    If Not fnd Is Nothing Then
        firstaddress = fnd.Address
        Do
            '... und alle Fundstellen mitsamt ihrer Zeile in eine neue Variable packen
            If tmpRange Is Nothing Then
                Set tmpRange = Intersect(SourceRange, fnd.EntireRow)
            Else
                Set tmpRange = Union(tmpRange, Intersect(SourceRange, fnd.EntireRow))
            End If
           
            Set fnd = SearchRange.FindNext(fnd)
        Loop While fnd.Address <> firstaddress
    End If
   
    'Fundbereich zurückgeben. Im Zweifel Nothing
    Set GetRowsWithMultiColumnMatch = tmpRange
End Function

Danke nochmal an alle Beteiligten