Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1520to1524
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Hilfe VBA

Hilfe VBA
24.10.2016 15:50:49
kurt
Hallo! :)
Ich brauche eure Hilfe. Ich habe eine Excel-Datei mit 2 Spalten. In einer zweiten Excel-Datei sind auch zwei Spalten. In den ersten Spalten steht immer "Name" (bei beiden Dateien gleich); in der zweiten Datei steht in der zweiten Spalte immer ja oder nein. Ich will mit VBA programmieren, dass, wenn bei der zweiten Datei "nein" steht, in der ersten Datei die Zeile mit dem selben Namen gelöscht wird. Kann mir vielleicht einer von euch weiterhelfen?

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Hilfe VBA
24.10.2016 16:24:27
UweD
Hallo
klappt das so?
- In der leeren Spalte C wird per Sverweis das ja/nein eingetragen
- Filter gesetzt
- Nein ausgewählt
- Alle gefilterten Zeilen gelöscht
- Spalte C wieder gelöscht
Sub LöschenMappe1()
    Dim TB1, Pfad As String, Datei As String, Blatt As String
    Dim LR As Long, EZ As Integer
    
    '-Vorgaben 
    Set TB1 = ThisWorkbook.Sheets("Tabelle1")
    Pfad = ThisWorkbook.Path & "\"
    Datei = "Mappe2.xlsx"
    EZ = 2 ' ggf Überschrift beachten 
    '-Ende Vorgaben 
    
    
    Datei = "[" & Datei & "]"
    With TB1
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
        .Range(Cells(EZ, 3), Cells(LR, 3)).FormulaR1C1 = _
            "=VLOOKUP(RC[-2],'" & Pfad & Datei & Blatt & "'!C1:C3,2,0)"
        .Cells(1, 3) = "Tmp"
        If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten 
        .Columns(3).AutoFilter
        .Columns(3).AutoFilter Field:=1, Criteria1:="nein"
        .Rows(EZ & ":" & LR).Delete xlUp
        .AutoFilterMode = False
        .Columns(3).ClearContents
    End With
End Sub

LG UweD
Anzeige
AW: Hilfe VBA
24.10.2016 16:40:55
kurt
Sub LöschenMappe1()
Dim TB1, Pfad As String, Datei As String, Blatt As String
Dim LR As Long, EZ As Integer
'-Vorgaben
Set TB1 = ThisWorkbook.Sheets("Tabelle1")
Pfad = "C:\...." & "\"
Datei = "Mappe_2.xlsx"
EZ = 2 ' ggf Überschrift beachten
'-Ende Vorgaben
Datei = "[" & Datei & "]"
With TB1
LR = .Cells(.Rows.Count, "A").End(xlUp).row 'letzte Zeile der Spalte
.Range(Cells(EZ, 3), Cells(LR, 3)).FormulaR1C1 = _
"=VLOOKUP(RC[-2],'" & Pfad & Datei & Blatt & "'!C1:C3,2,0)"
        .Cells(1, 3) = "Tmp"
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Columns(3).AutoFilter
.Columns(3).AutoFilter Field:=1, Criteria1:="nein"
.Rows(EZ & ":" & LR).Delete xlUp
.AutoFilterMode = False
.Columns(3).ClearContents
End With
End Sub
Laufzeitfehler 1004
Anwendungs- oder objektdefinierter Fehler
Bei der 2. Datei ist die "nein/ja" Spalte die 6 Spalte, wie kann ich das Anpassen?
Anzeige
AW: Hilfe VBA
24.10.2016 16:58:06
Bastian
Halli Hallo im dem Code fehlt der Brzug auf das Blatt von Mappe2
Gruß Basti
Sub LöschenMappe1()
Dim TB1, Pfad As String, Datei As String, Blatt As String
Dim LR As Long, EZ As Integer
'-Vorgaben
Set TB1 = ThisWorkbook.Sheets("Tabelle1")
Pfad = ThisWorkbook.Path & "\"
Datei = "Mappe2.xlsx"
Blatt = "Tabelle1"
EZ = 2 ' ggf Überschrift beachten
'-Ende Vorgaben
Datei = "[" & Datei & "]"
With TB1
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
.Range(Cells(EZ, 3), Cells(LR, 3)).FormulaR1C1 = _
"=VLOOKUP(RC[-2],'" & Pfad & Datei & Blatt & "'!C1:C3,2,0)"
.Cells(1, 3) = "Tmp"
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Columns(3).AutoFilter
.Columns(3).AutoFilter Field:=1, Criteria1:="nein"
.Rows(EZ & ":" & LR).Delete xlUp
.AutoFilterMode = False
.Columns(3).ClearContents
End With
End Sub

Anzeige
AW: Hilfe VBA
24.10.2016 17:00:28
UweD
Hallo nochmal
Uppps, hatte das Blatt vergessen zu benennen.
Hab die Formel noch mit Wennfehler() versehen und ohne die R1C1 Version verwendet.
Ich hoffe da wird es klarer.

Sub LöschenMappe2()
Dim TB1, Pfad As String, Datei As String, Blatt As String
Dim LR As Long, EZ As Integer
'-Vorgaben
Set TB1 = ThisWorkbook.Sheets("Tabelle1")
Pfad = "C:\Temp" & "\"
Datei = "Mappe_2.xlsx"
Blatt = "Tabelle1"
EZ = 2 ' ggf Überschrift beachten
'-Ende Vorgaben
Datei = "[" & Datei & "]"
With TB1
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
.Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
"=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$A:$F,6,0),"""")"
.Cells(1, 3) = "Tmp"
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Columns(3).AutoFilter
.Columns(3).AutoFilter Field:=1, Criteria1:="nein"
.Rows(EZ & ":" & LR).Delete xlUp
.AutoFilterMode = False
.Columns(3).ClearContents
End With
End Sub

LG UweD
Anzeige
AW: Hilfe VBA
24.10.2016 17:25:14
kurt
Hallo
Jetzt läuft alles fehlerfrei. Das einzige ist, dass bei der ersten Datei, anstatt der dritten Spalte und alle in der zweiten Datei auf "nein" gesetzten Zeilen, alles gelöscht wird.

Sub PrüfungWerte()
Dim TB1, Pfad As String, Datei As String, Blatt As String
Dim LR As Long, EZ As Integer
'-Vorgaben
Set TB1 = ThisWorkbook.Sheets("Tabelle1")
Pfad = "C:\..." & "\"
Datei = "Mappe_2.xlsx"
Blatt = "Tabelle1"
EZ = 2 ' ggf Überschrift beachten
'-Ende Vorgaben
Datei = "[" & Datei & "]"
With TB1
LR = .Cells(.Rows.Count, "A").End(xlUp).row 'letzte Zeile der Spalte
.Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
"=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$A:$F,6,0),"""")"
.Cells(1, 3) = "Tmp"
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Columns(3).AutoFilter
.Columns(3).AutoFilter Field:=1, Criteria1:="nein"
.Rows(EZ & ":" & LR).Delete xlUp
.AutoFilterMode = False
.Columns(3).ClearContents
End With
Sheets("Wenige_Veränderungen_365t").Protect "riskoffice"
End Sub

Anzeige
AW: Hilfe VBA
25.10.2016 08:23:23
UweD
Hallo
Bei mir läuft es einwandfrei.
Das Phänomen tritt nur auf, wenn die angegebene Datei2 nicht gefunden wird.
Dann läuft das Makro troztdem weiter. Hier hab ich noch eine Prüfung reingesetzt
Was mir aber auffällt. Du sperrst am Ende ein Blatt. Das kann aber nicht das Blatt sein, wo gerade die Änderungen gemacht wurden oder?
Dann passen die Blattnamen nicht zueinander.
- oben steht "Tabelle1"
- unten "Wenige_Veränderungen_365t"
Sub PrüfungWerte()

    Dim TB1, Pfad As String, Datei As String, Blatt As String
    Dim LR As Long, EZ As Integer
    
    '-Vorgaben 
    Set TB1 = ThisWorkbook.Sheets("Tabelle1")
    Pfad = "C:\Temp" & "\"
    Datei = "Mappe_2.xlsx"
    Blatt = "Tabelle1"
    EZ = 2 ' ggf Überschrift beachten 
    '-Ende Vorgaben 
    
    
    If Dir(Pfad & Datei) = "" Then
        MsgBox "Datei fehlt!"
        Exit Sub
    End If
    
    Datei = "[" & Datei & "]"
    With TB1
        LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
        .Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
            "=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$A:$F,6,0),"""")"
        .Cells(1, 3) = "Tmp"
        If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten 
        .Columns(3).AutoFilter
        .Columns(3).AutoFilter Field:=1, Criteria1:="nein"
        .Rows(EZ & ":" & LR).Delete xlUp
        .AutoFilterMode = False
        .Columns(3).ClearContents
    End With
    Sheets("Wenige_Veränderungen_365t").Protect "riskoffice"


End Sub
- Prüfe das Makro mal im Schrittmodus (F8)
- besonders, nachdem die Formeln eingetragen wurden
LG UweD
Anzeige
AW: Hilfe VBA
25.10.2016 08:55:11
kurt
Hallo,
-so wie es aussieht, wird die Datei gefunden, da die msgbox nicht auftritt.
-ich habe das Makro mi f8 geprüft. Es wird tatsächlich eine dritte Spalte "tmp" eingefügt, Problem ist aber, dass die Spalte aus der zweite Datei, wo nein oder ja steht (6. Spalte), nicht eingefügt wird.
-beim schritt .Rows(EZ & ":" & LR).Delete xlUp werden alle Zeilen von der ersten Datei gelöscht
-vielleicht ist das auch wichtig: die Daten aus beide Tabellen fangen erst ab der 4 Zeile an

Sub PrüfungWerte()
Dim TB1, Pfad As String, Datei As String, Blatt As String
Dim LR As Long, EZ As Integer
'-Vorgaben
Set TB1 = ThisWorkbook.Sheets("Tabelle1")
Pfad = "C:\..." & "\"
Datei = "Mappe_2.xlsx"
Blatt = "Tabelle1"
EZ = 2 ' ggf Überschrift beachten
'-Ende Vorgaben
If Dir(Pfad & Datei) = "" Then
MsgBox "Datei fehlt!"
Exit Sub
End If
Datei = "[" & Datei & "]"
With TB1
LR = .Cells(.Rows.Count, "A").End(xlUp).row 'letzte Zeile der Spalte
.Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
"=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$A:$F,6,0),"""")"
.Cells(1, 3) = "Tmp"
If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten
.Columns(3).AutoFilter
.Columns(3).AutoFilter Field:=1, Criteria1:="nein"
.Rows(EZ & ":" & LR).Delete xlUp
.AutoFilterMode = False
.Columns(3).ClearContents
End With
End Sub

Anzeige
AW: Hilfe VBA
25.10.2016 09:07:49
UweD
Hallo nochmal
Lad mal bitte die 2 Dateien hier hoch
  • ich vermute Leerzeichen vor/hinter den Einträgen in Spalte A
    oder ZAhlen als Text etc.
    so dass der Vergleich keinen Treffer liefert

  • änder mal das hier und sieh dir das Blatt beim Stop an.
    
    .Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
    "=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$A:$F,6,0),""Unbekannt"")"
    Stop
    .Cells(1, 3) = "Tmp"
    

    LG UweD
    AW: Hilfe VBA
    25.10.2016 10:09:50
    kurt
    ich habe das Problem gefunden. Die Spalte A von der zweite Datei ist leer. Wenn ich das ganze um eine Spalte nach links schiebe, funktioniert es. Wie kann ich es so anpassen, dass die Spalte leer sein kann und es trotzdem funktioniert?
    Anzeige
    AW: Hilfe VBA
    25.10.2016 10:20:57
    UweD
    Hallo
    dann sind die Daten also in B:F
    "=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$B:$F,5,0),""Unbekannt"")"
    Es wird also in Spalte B gesucht und die 5. Spalte (ab B gerechnet) also aus F zurückgegeben.
    AW: Hilfe VBA
    27.10.2016 18:52:45
    kurt
    Hallo, UweD!
    Zunächst wollte ich mich für deine ganze Hilfe bedanken! :)
    Ich hab auch nur noch eine Frage. Falls ich die Spalten nicht löschen wollen würde, sondern sie nur ausblenden, wie müsste ich das ganze abändern?
    .Columns(3).AutoFilter Field:=1, Criteria1:="nein"
    .Rows(EZ & ":" & LR).Hidden = True
    .AutoFilterMode = False
    
    Ich hätte es so gemacht, es klappt aber nicht wirklich. Hast du noch eine Idee?
    Vielen Dank (nochmals)!
    Anzeige
    AW: Hilfe VBA
    28.10.2016 08:50:41
    UweD
    Hallo kurt
    Filtern ist (fast) wie ausblenden.
    wenn du also gefilterte Zeilen ausblendest und dann den Filter wieder wegnimmst,
    werden die Zeilen auch wieder sichtbar.
    Ein workarround wäre z.b das minimieren der Zeilenhöhe..
    Sub LöschenMappe2()
        Dim TB1, Pfad As String, Datei As String, Blatt As String
        Dim LR As Long, EZ As Integer, ZH As Integer
        
        '-Vorgaben 
        Set TB1 = ThisWorkbook.Sheets("Tabelle1")
        Pfad = "C:\Temp" & "\"
        Datei = "Mappe_2.xlsx"
        Blatt = "Tabelle1"
        EZ = 2 ' ggf Überschrift beachten 
        ZH = 15 'Standardzeilenhöhe 
        '-Ende Vorgaben 
        
        
        Datei = "[" & Datei & "]"
        With TB1
            LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte 
            .Range(Cells(EZ, 3), Cells(LR, 3)).Formula = _
                "=IFERROR(VLOOKUP(A2,'" & Pfad & Datei & Blatt & "'!$B:$F,5,0),""Unbekannt"")"
            .Cells(1, 3) = "Tmp"
            If .AutoFilterMode Then .AutoFilterMode = False ' Autofilter ausschalten 
            .Cells.EntireRow.RowHeight = ZH 'alle Zeilen auf Standardhöhe setzen 
            If WorksheetFunction.CountIf(Columns(3), "nein") > 0 Then
                .Columns(3).AutoFilter Field:=1, Criteria1:="nein"
                .Rows(EZ & ":" & LR).RowHeight = 0.1 'fast ausblenden 
            .AutoFilterMode = False
            End If
            .Columns(3).ClearContents
        End With
    End Sub
    

    LG UweD
    AW: Hilfe VBA
    28.10.2016 11:13:55
    kurt
    Hallo!
    Das funktioniert zunächst, wenn ich aber die Zellen kopieren will, werden auch die "ausgeblendeten" kopiert, Gibt es nicht eine Möglichkeit, sie richtig auszublenden, sodass sie nicht beim kopieren übertragen werden?
    VG
    AW: Hilfe VBA
    28.10.2016 12:01:23
    kurt
    Hallo,
    eine Alternative, die mir auch weiterhelfen würde:
    - wie kann ich die ersten zwei Spalten aus der Mappe 2 in die erste Datei Kopieren? (Spalte A&B).
    - bei der zweiten Mappe fangen die Werte ab der 3. Zeile an und sollen in die Mappe 1 kopiert werden, Dies soll jedoch nicht ab der ersten zeile, sondern erst ab der 4. geschehen.
    VG
    AW: Hilfe VBA
    28.10.2016 12:43:57
    UweD
    Du kannst den Filter doch umdrehen.
    Du musst doch nicht ausblenden oder löschen.
    Du kannst doch sofort nach dem Filtern die Daten kopieren (auch per Makro),
    dabei werden die ausgefilterten NICHT mitkopiert.
    der erste Satz sollte weg..
    28.10.2016 12:46:29
    UweD
    Du kannst den Filter doch umdrehen.
    Du musst doch nicht ausblenden oder löschen.
    Du kannst doch sofort nach dem Filtern die Daten kopieren (auch per Makro),
    dabei werden die ausgefilterten NICHT mitkopiert.
    AW: Hilfe VBA
    28.10.2016 13:05:26
    kurt
    Hallo UweD,
    -was ich jetzt noch gemacht habe, ist einen Button erstellen. Wenn man raufklickt werden alle "nein"-Daten gelöscht. (So wie am Anfang geplant)
    -was ich jetzt noch machen will, ist einen zweiten Button, der die Inhalte in Spalte A & B ab Zeile 4 löscht und die Werte aus Spalta A und B aus der 2. Mappe (ab Zeile 3) ind die Mappe 1 (ab Zeile 4) kopiert
    'Vorgaben
    ThisWorkbook.Sheets("Tabelle1").Range("A3:B1000000").ClearContents
    Datei = "[" & Datei & "]"
    With TB1
    .Range("A3").Copy Sheets("Tabelle1").Range("A4")
    .Range("B3").Copy .Sheets("Tabelle1").Range("B4")
    End With
    

    ich hatte mir so etwas vorgestellt, es klappt aber nicht
    Ich hoffe, dass du mir helfen kannst
    AW: Hilfe VBA
    28.10.2016 13:50:22
    kurt
    Hallo UweD!
    Vielen, vielen Dank für deine Hilfe und mühe! Ich habe jetzt eine Lösung und alles funktioniert einwandfrei!
    MfG
    Kurt
    Ok, danke für die Rückmeldung.
    28.10.2016 14:35:24
    UweD

    Beliebteste Forumthreads (12 Monate)

    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige