Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Zellinhalt per VBA suchen und kopieren

Zellinhalt per VBA suchen und kopieren
18.05.2017 11:20:27
Nitokris
Hallo VBA-Experten,
ich habe folgendes Problem, das sich vermutlich ohne VBA nicht lösen lässt:
Ausgangspunkt ist eine Tabelle, in der Zuordnungen zu verschiedenen Gebäuden enthalten sind:
A B C
1 Gebäude Schuppen
2 Werkzeug Schaufel
3 Werkzeug Gießkanne
4 Sonstige Winterräder
5
6 Gebäude Haus
7 Bauteil Tür
8 Bauteil Fenster
Ich möchte, dass Excel
1) in Spalte A nach dem Begriff "Gebäude" sucht,
2) den Begriff der rechten Nachbarzelle (also Spalte B) wählt und
3) diesen in Spalte C immer wieder untereinander kopiert, und zwar solange, bis in Spalte A das nächste Mal das Wort "Gebäude" auftaucht.
4) Wiederholung des Vorgangs so lange, bis die Tabelle zu Ende ist, was durch den Schlüsselbegriff "ende" in Spalte A gekennzeichnet ist.
Aussehen soll die Tabelle dann also so:
A B C
1 Gebäude Schuppen Schuppen
2 Werkzeug Schaufel Schuppen
3 Werkzeug Gießkanne Schuppen
4 Sonstige Winterräder Schuppen
5
6 Gebäude Haus Haus
7 Bauteil Tür Haus
8 Bauteil Fenster Haus
Hat jemand eine Idee, wie das mit einem (hoffentlich) halbwegs einfachen Code zu lösen ist? Ich bin leider über die Erstellung eines Moduls in VBA noch nicht herausgekommen.
Ich bin dankbar für jede Hilfe.
Danke, Nitokris
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
18.05.2017 12:41:05
ChrisL
Hi
Könnte man mit einer einfachen Formel lösen.
D2:
=WENN(B2B$2;D1;C2)
cu
Chris
AW: Zellinhalt per VBA suchen und kopieren
18.05.2017 14:16:04
Nitokris
Hallo Chris,
die von dir vorgeschlagene Lösung ist mir formelmäßig vollkommen klar, sie tut nur nicht das, was ich beschrieben habe. Ich befürchte, meine Beispieltabelle war nicht deutlich genug - ich versuche mal, die Tabelle hochzuladen:
https://www.herber.de/bbs/user/113676.xlsx
Darüber hinaus ist die von mir eingestellte Tabelle ein Minimalbeispiel - in der Regel habe ich große Dateien, die um die 1000 Zeilen haben. Eine Umsetzung via Formeln wäre dafür viel zu umständlich, da ich dann jede Tabelle händisch damit füllen müsste. Das ist ja überhaupt der Grund, warum ich hoffe, bei VBA fündig zu werden - um eine Lösung zu finden, die eine Vielzahl großer Tabellen mit wenigen Handgriffen füllt, ohne, dass ich jedesmal wieder Formeln reinkopieren muss.
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
18.05.2017 14:25:25
ChrisL
Hi
Einfach um eine Spalte verschieben.
C2:
=WENN(B2B$2;D1;C2)
Und wenn da noch leere Zeilen dazwischen sind:
=WENN(A2="";"";WENN(A2A$2;C1;B2))
Und die Formel in VBA verwurstet:
Sub mach()
Call WerteEinsetzen(Worksheets("Tabelle1"))
Call WerteEinsetzen(Worksheets("Tabelle2"))
'usw.
End Sub

Private Sub WerteEinsetzen(ws As Worksheet)
With ws
With .Range("C2:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
.Formula = "=IF(A2="""","""",IF(A2A$2,C1,B2))"
.Value = .Value
End With
End With
End Sub
cu
Chris
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
18.05.2017 14:44:44
yummi
Hallo Nikrotis,
wenn du unbedingt per VBA willst:

Sub FuelleAuf()
Dim i As Long
Dim wks As Worksheet
Dim strmerke As String
Dim bende As Boolean
Set wks = ActiveSheet
i = 2
bende = False
Do While bende = False
If wks.Cells(i, 1).Value  "" Then
If wks.Cells(i, 1).Value = "Gebäude " Then
strmerke = wks.Cells(i, 2).Value
End If
wks.Cells(i, 3).Value = strmerke
Else
If wks.Cells(i + 1, 1).Value = "" Then
bende = True
End If
End If
i = i + 1
Loop
End Sub
Gruß
yummi
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
22.05.2017 10:41:56
Nitokris
Hallo Yummi, vielen Dank, dein Code funktioniert hervorragend.
Eine Frage habe ich noch: Hin und wieder befindet sich mehr als eine Leerzeile zwischen den einzelnen "Blöcken". Wie muss ich den Code verändern, damit dies ignoriert wird? Bis jetzt wird das Eintragen der Werte in Spalte C beendet, sobald mehr als eine Leerzeile auftaucht.
Viele Grüße
Nitokris
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
23.05.2017 08:44:01
yummi
Hallo Nikrotis,
hier die Schleife mal mit kommentaren:

Do While bende = False                  'solange bende = true mache das folgende
If wks.Cells(i, 1).Value  "" Then   'nur wenn in zeile i Spalte A etwas steht
If wks.Cells(i, 1).Value = "Gebäude " Then   'nur wenn in Zeile i Spalte A das Wort  _
Gebäude steht
strmerke = wks.Cells(i, 2).Value  'merke dir den WErt der in Zeile i Spalte B  _
steht
End If
wks.Cells(i, 3).Value = strmerke      'schreibe in Zeile i Spalte c den gemerkten  _
Wert
Else
If wks.Cells(i + 1, 1).Value = "" Then   'zeile i Spalte A ist leer und hier wird  _
abgefragt, ob Zeiel i + 1 auch leer ist
bende = True      ' Kriterium für Schleifenende setzen
End If
End If
i = i + 1         'laufvariable für Zeile inkrementieren
Loop
Du siehst auf die Art und weise kannst Du nicht abfragen, ob beliebig viele Leerzeilen kommen. Du könntest es aber so lösen:

Sub FuelleAuf()
Dim i As Long
Dim wks As Worksheet
Dim strmerke As String
Dim bende As Boolean
Set wks = ActiveSheet
llast = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
for i = 2 to llast
If wks.Cells(i, 1).Value  "" Then
If wks.Cells(i, 1).Value = "Gebäude " Then
strmerke = wks.Cells(i, 2).Value
End If
wks.Cells(i, 3).Value = strmerke
end if
next i
End Sub
Sollte so gehen, habs aber nicht getestet.
gruß
yummi
Anzeige
AW: Zellinhalt per VBA suchen und kopieren
22.05.2017 22:33:50
Gerd

Hallo Nitrokris!
Sub Fuelle_auf()
Dim Areal As Range, Rng As Range
On Error Resume Next
Set Areal = Columns(1).SpecialCells(2)
On Error GoTo 0
If Not Areal Is Nothing Then
For Each Rng In Areal.Areas
If Rng(1, 1) = "Gebäude" Then Rng.Offset(, 2) = Rng(1, 2)
Next
Set Areal = Nothing
End If
End Sub

Gruß Gerd
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Zellinhalt per VBA suchen und kopieren


Schritt-für-Schritt-Anleitung

Um in Excel den Zellinhalt per VBA zu suchen und zu kopieren, kannst du die folgenden Schritte ausführen:

  1. Öffne den VBA-Editor:

    • Drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Erstelle ein neues Modul:

    • Klicke auf Einfügen > Modul.
  3. Füge den folgenden VBA-Code ein:

    Sub FuelleAuf()
       Dim i As Long
       Dim wks As Worksheet
       Dim strmerke As String
       Dim bende As Boolean
       Set wks = ActiveSheet
       i = 2
       bende = False
       Do While bende = False
           If wks.Cells(i, 1).Value <> "" Then
               If wks.Cells(i, 1).Value = "Gebäude" Then
                   strmerke = wks.Cells(i, 2).Value
               End If
               wks.Cells(i, 3).Value = strmerke
           Else
               If wks.Cells(i + 1, 1).Value = "" Then
                   bende = True
               End If
           End If
           i = i + 1
       Loop
    End Sub
  4. Schließe den VBA-Editor und gehe zurück zu Excel.

  5. Führe das Makro aus:

    • Drücke ALT + F8, wähle FuelleAuf aus und klicke auf Ausführen.

Häufige Fehler und Lösungen

  • Problem: Der Code funktioniert nicht, wenn Leerzeilen vorhanden sind.

    • Lösung: Ändere den Code wie folgt, um mehrere Leerzeilen zu überspringen:
      For i = 2 To llast
      If wks.Cells(i, 1).Value <> "" Then
          If wks.Cells(i, 1).Value = "Gebäude" Then
              strmerke = wks.Cells(i, 2).Value
          End If
          wks.Cells(i, 3).Value = strmerke
      End If
      Next i
  • Problem: Der VBA-Code kopiert nicht die gewünschten Werte.

    • Lösung: Stelle sicher, dass die Bedingungen im Code korrekt sind. Überprüfe, ob der Begriff "Gebäude" exakt so geschrieben ist.

Alternative Methoden

Wenn du Excel suchen und kopieren möchtest, ohne VBA zu verwenden, kannst du auch eine Formel nutzen:

  1. Gehe zu Zelle C2 und gib folgende Formel ein:
    =WENN(A2="";"";WENN(A2="Gebäude";B2;C1))
  2. Ziehe die Formel nach unten, um die anderen Zeilen auszufüllen.

Beachte, dass dies bei großen Tabellen umständlich sein kann, insbesondere wenn du oft Werte in Excel zeilenweise kopieren möchtest.


Praktische Beispiele

Ein praktisches Beispiel für die Nutzung des VBA-Codes könnte wie folgt aussehen:

  • Tabelle:

    A          B
    1 Gebäude   Schuppen
    2 Werkzeug  Schaufel
    3 Werkzeug  Gießkanne
    4 Sonstige  Winterräder
    5
    6 Gebäude   Haus
    7 Bauteil   Tür
  • Erwartetes Ergebnis in Spalte C:

    C
    1 Schuppen
    2 Schuppen
    3 Schuppen
    4 Schuppen
    5
    6 Haus
    7 Haus

Tipps für Profis

  • Code optimieren: Versuche, den Code so zu gestalten, dass er flexibel ist, um auch mit Tabellen zu arbeiten, die nicht immer gleich aufgebaut sind.

  • Verwendung von Option Explicit: Füge dies oben im Modul hinzu, um sicherzustellen, dass alle Variablen deklariert sind. Das hilft, Fehler zu vermeiden.

  • Testen des Codes: Teste den Code immer mit einer Kopie deiner Daten, um unerwartete Ergebnisse zu vermeiden.


FAQ: Häufige Fragen

1. Wie kann ich den Code anpassen, um andere Begriffe zu suchen? Du kannst einfach den Begriff "Gebäude" im Code durch den gewünschten Suchbegriff ersetzen.

2. Funktioniert dieser Code in allen Excel-Versionen? Ja, der VBA-Code ist mit den meisten Excel-Versionen kompatibel, die VBA unterstützen, einschließlich Excel 2010 und neuer.

3. Kann ich den Code für andere Spalten anpassen? Ja, du kannst die Spaltenindizes im Code ändern, um mit anderen Spalten zu arbeiten. Achte darauf, die Logik entsprechend anzupassen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige