12.07.2009, aktualisiert um 11:13:44 Uhr

92762

Zelleninhalt per VBA in einer Spalte suchen

Hallo,
meine VBA-Kenntnisse sind noch nicht so weit fortgeschritten, besitze aber Programmiererfahrung in Java, von daher klappts bisher eigentlich ganz gut.
Dennoch bin ich jetzt auf ein Problem gestoßen:
Ich habe in Tabellenblatt 1 eine Zelle, deren Inhalt in Tabellenblatt2 nur in Spalte A gesucht werden soll. Um zu prüfen, ob das funktioniert hab ich einfachheitshalber mal 2 msgboxen eingebaut, aber leider klappts mit der bedingung schon nicht.

ich wollte das ganze mit einer IF-Schleife machen, weil dann noch div. andere Aktionen folgen, wenn der Wert in Tabellenblatt 2 vorhanden ist.
Hier mal meine If-Schleife, vlt, kann mir jemand sagen, warum das nicht funktioniert

If Worksheets("Tabellenblatt1").Cells(2,6) = Worksheets("Tabellenblatt2").Range(Columns("A:A")) Then

MsgBox "Wurde gefunden"

Else
MsgBox "nicht vorhanden"

Dankeschön, schon mal vorab!

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 120279

Url: https://administrator.de/contentid/120279

Ausgedruckt am: 23.11.2024 um 02:11 Uhr

23 Kommentare

Neuester Kommentar

Hallo Schnufflchen!

Probiers mal damit:

Private Sub Test()
  Dim Wks1 As Worksheet, Wks2 As Worksheet, Found As Object
    
  Set Wks1 = Sheets("Tabelle1")  
  Set Wks2 = Sheets("Tabelle2")  
    
  If Not IsEmpty(Wks1.Cells(2, 6)) Then

     Set Found = Wks2.Columns(1).Find(Wks1.Cells(2, 6), LookIn:=xlValues, LookAt:=xlWhole)
    
     If Found Is Nothing Then
          MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
     Else
          MsgBox "Wert in Zelle " & Found.Address, vbInformation, "Meldung"  
     End If
   End If
End Sub

Gruß Dieter

[edit] If-Abfrage für Zelle = Leer hinzugefügt [/edit]

Das ist super! Dankeschön. Eine Sache hab ich aber noch: Mir wird auch wenn der Wert nicht in der Spalte enthalten ist die Box ausgegeben, dasss der wert enthalten ist. wie bekomm ich das hin?

Hallo Schnufflchen!

Ups, daran hatte ich nicht gedacht

Code oben geändert.

Gruß Dieter

Herzlichen Dank did1954,
du hast mir sooooo geholfen! Jetzt kann ich wieder weiterarbeiten!

Hallo Schnufflchen!

Gern Geschehen

Falls Du auch Text suchst, in dem NICHT zwischen Groß/Kleinschreibung unterschieden werden soll,
dann füge am Blattanfang noch folgendes ein:

Option Compare Text

Gruß Dieter

Moin moin,

Ich möchte den Inhalt von drei Zellen in der selben Zeile wie die gefundene Zelle kopieren, Ich habe mir gedacht, wenn ich die gefundene Zelle aktivieren kann, könnte ich das mit ActiveCell.Offset(0, 6).Range("A1").Select (wofür das A1 bei der Range?) aber ich bin ein VBA-Neuling und hab so noch keine wirkliche Idee, hier mein bisheriger Code, die Definitionen am Anfang sind Allgemeingültig und ich kopier sie aus Faulheit immer nur.

Dim WB As Workbook, WS1, WS2, WS3, WS4 As Worksheet
Dim i, LetzteZeileWS1, LetzteZeileWS2 As Long
Dim Dateiname2009, Dateiname2010, Suche As String
Dim Found As Object

Sub ABC()

Dateiname1 = "1.xls"
Dateiname2 = "2.xls"

Set WS1 = Workbooks(Dateiname1).Sheets("daten")
Set WS2 = Workbooks(Dateiname2).Sheets("daten")

LetzteZeileWS1 = WS1.Cells(Rows.Count, 1).End(xlUp).Row
LetzteZeileWS2 = WS2.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LetzteZeileWS2

If IsEmpty(WS2.Cells(i, 8)) Then

Set Found = WS1.Columns(2).Find(WS2.Cells(i, 2), LookIn:=xlValues, LookAt:=xlWhole)

If Found Is Nothing Then
' MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"
Else
MsgBox "Wert in Zelle " & Found.Address, vbInformation, "Meldung"

End If
End If

Next

End Sub

Liebe Grüße

Hallo s1rp4t!

In etwa so:

If Found Is Nothing Then
    MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
Else
    'Beispiel: Kopiert die gefundene Zelle plus 3 weitere Zellen, also 4 Zellen nach A1-D1  
    Range(Found, Found.Offset(0, 3)).Copy Destination:=Range("A1")  
    
    'Beispiel: Kopiert von der gefundenen Zelle die erste Zelle daneben plus 3 weitere Zellen, auch 4 Zellen nach A1-D1  
    Range(Found.Offset(0, 1), Found.Offset(0, 4)).Copy Destination:=Range("A1")  
End If

Wobei die Variable Set Found das Range-Object der gefundenen Zelle repräsentiert.

Gruß Dieter

PS. Wenn sich die zu kopierenden Zellen links von der gefundenen Zelle befinden, dann für den Spalten-Offset ein Minuszeichen voranstellen z.B. Offset(0, -4)

Vielen Dank für die schnelle Antwort, hab es leider erst jetzt lesen können.

Mein If-Tag sieht wie folgt aus, leider macht er mir immer Probleme mit dem copy und dem paste

If Found Is Nothing Then
MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"
Else
'Beispiel: Kopiert die gefundene Zelle plus 3 weitere Zellen, also 4 Zellen nach A1-D1

Range(Found, Found.Offset(0, 3)).Copy Destination:=Range("A1")
Windows(Dateiname1).Activate
## ActiveSheet.Cells(i, 8).Select
ActiveSheet.Paste

Range(Found, Found.Offset(0, 3)).Copy Destination:=Range("A1")
ActiveSheet(i, 9).Select
ActiveSheet.Paste
Windows(Dateiname1).Activate

Range(Found, Found.Offset(0, 3)).Copy Destination:=Range("A1")
ActiveSheet(i, 10).Select
ActiveSheet.Paste
Windows(Dateiname1).Activate
'Beispiel: Kopiert von der gefundenen Zelle die erste Zelle daneben plus 3 weitere Zellen, auch 4 Zellen nach A1-D1
'Range(Found.Offset(0, 1), Found.Offset(0, 4)).Copy Destination:=Range("A1")

End If

Statt >>Activesheet(i, 10).Select<< hatte ich >>WS1.Cells(i, 10).Select<< probiert, das wollte er nicht, jetzt gibt er mir die Fehlermeldungen
"Die Paste-Methode des Worksheet-Objektes konnte nicht ausgeführt werden" bei ##

Hallo s1rp4t!

Also, den Paste-Befehl brauchst Du nicht, weil z.B. diese Zeile

Range(Found.Offset(0, 1), Found.Offset(0, 4)).Copy Destination:=Range("A1")

den Kopier- und Einfügvorgang schon beinhaltet. D.h. Destination (Ziel) steht für die Einfüg-Zelle.

Dieses Code-Beispiel sollte Dich Deinem Ziel etwas näher bringen (Offsets bei Bedarf anpassen):

Sub ABC()
    Dim WB As Workbook, WS1 As Worksheet, WS2 As Worksheet, Found As Object
    Dim i As Long, LetzteZeileWS1 As Long, LetzteZeileWS2 As Long
    Dim Dateiname1 As String, Dateiname2 As String, Suche As String

    Dateiname1 = "1.xls"  
    Dateiname2 = "2.xls"  
    
    Set WS1 = Workbooks(Dateiname1).Sheets("daten")  
    Set WS2 = Workbooks(Dateiname2).Sheets("daten")  
    
    LetzteZeileWS1 = WS1.Cells(WS1.Rows.Count, 1).End(xlUp).Row
    LetzteZeileWS2 = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row
    
    For i = 1 To LetzteZeileWS2
    
        If Not IsEmpty(WS2.Cells(i, 2)) And IsEmpty(WS2.Cells(i, 8)) Then
        
            Set Found = WS1.Columns(2).Find(WS2.Cells(i, 2), LookIn:=xlValues, LookAt:=xlWhole)
            
            If Found Is Nothing Then
                MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
            Else
                Range(Found.Offset(0, 6), Found.Offset(0, 8)).Copy Destination:=WS2.Cells(i, 8)
            End If
        End If
    Next
End Sub

In Makros benötigst Du keine Activate und Select-Anweisungen, da Du Tabellen über Namen und Zellen über Adressen explizit ansprechen kannst.

Gruß Dieter

Vielen Dank, ich werde das bis zum Mittag mal ausprobieren und mich bei Bedarf noch mal melden.

Liebe Grüße

So ich habe jetzt noch einen draufgesetzt (man muss sich ja steigern

)

Das ist ein Code für eine andere Tabelle.

For i = 2 To 1103

    If Not IsEmpty(WS1.Cells(i, 1)) Then
            Set Found = WS2.Columns(1).Find(WS1.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Found Is Nothing Then
                'MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
            Else
##          Set Found2 = WS3.Columns(1).Find(WS2.Cells(Found.Offset(0, 1)), LookIn:=xlValues, LookAt:=xlWhole)
                Range(Found2.Offset(0, 5)) = 1
            End If
        End If

    If Not IsEmpty(WS1.Cells(i, 2)) Then
            Set Found = WS2.Columns(1).Find(WS1.Cells(i, 2), LookIn:=xlValues, LookAt:=xlWhole)
            If Found Is Nothing Then
                'MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
            Else
            Set Found2 = WS3.Columns(1).Find(WS2.Cells(Found.Offset(0, 1)), LookIn:=xlValues, LookAt:=xlWhole)
                Range(Found2.Offset(0, 5)) = 2
            End If
        End If
        
    If Not IsEmpty(WS1.Cells(i, 4)) Then
            Set Found = WS2.Columns(1).Find(WS3.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
            Set Found2 = WS1.Columns(4).Find(WS2.Cells(Found.Offset(0, -1)), LookIn:=xlValues, LookAt:=xlWhole)
                
            If Found2 Is Nothing Then
            
                WS3.Cells(i, 6) = 3
                
            Else
            
            End If
        End If

Next

Er gibt mir die Meldung "Anwendungs- oder Objektdefinierter Fehler." bei ## aus.

An WS3 oder Found liegt es nicht, ich wüsste aber nicht wieso Found2 Probleme machen sollte...

[Edit Biber] Codetax. Als ich jung war, konnte ich auch noch ohne lesen...[/Edit]

Vielleicht noch kurz der Hinweis wie ich meine Variablen definiert habe:

Dim sDatei As String, sPfad As String, i As Long
Dim WB As Workbook, WS1, WS2, WS3, WS4 As Worksheet
Dim LetzteZeileWS1, LetzteZeileWS2 As Long
Dim Dateiname1, Dateiname2, Suche As String
Dim Found, Found2 As Object

Liebe Grüße

Hallo s1rp4t!

Die Variable Set Found beinhaltet schon die komplette Adresse Arbeitsmappe.Tabellenblatt.Range. Von daher nur die Variable Found angeben.
Also anstatt:

Set Found2 = WS3.Columns(1).Find(WS2.Cells(Found.Offset(0, 1)), LookIn:=xlValues, LookAt:=xlWhole) 

so schreiben:

Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole) 

Das gleiche gilt auch für Zeile 18 und 25. Wobei Du das Found2-Ergebnis "If Found2 Is Nothing" (nicht gefunden) oder "If Not Found2 Is Nothing" (gefunden) von Zeile 18 nicht ausgewertet hast

Noch ein Hinweis zum deklarieren Deiner Variablen. Wenn Du z.B.

Dim x, y, z As Long

schreibst, dann ist nur die Variable z als Long definiert. Die Variablen x und y dagegen als Variant. Also ohne Angabe vor dem nächsten Komma immer Variant und steht für alle Datentypen.

Gruß Dieter

[Edit] Und ich Schussel hätte ja darauf hinweisen können, den Code in Code-Tags zu setzen (siehe Formatierungshilfe), aber Biber hat's mal wieder gerichtet

[/edit]

Nochmals Danke

For i = 2 To 1103

    If Not IsEmpty(WS1.Cells(i, 1)) Then
            Set Found = WS2.Columns(1).Find(WS1.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
            If Found Is Nothing Then
                'MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
            Else
           Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
##              WS3.Cells(Found2.Offset(0, 5)) = 1
            End If
        End If

    If Not IsEmpty(WS1.Cells(i, 2)) Then
            Set Found = WS2.Columns(1).Find(WS1.Cells(i, 2), LookIn:=xlValues, LookAt:=xlWhole)
            If Found Is Nothing Then
                'MsgBox "Wert nicht gefunden!", vbInformation, "Meldung"  
            Else
            Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
                Range(Found2.Offset(0, 5)) = 2
            End If
        End If
        
    If Not IsEmpty(WS1.Cells(i, 4)) Then
            Set Found = WS2.Columns(1).Find(WS3.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
            Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
                
            If Found2 Is Nothing Then
            
                WS3.Cells(i, 6) = 3
                
            Else
            
            End If
        End If

Next

Jetzt sagt er mir "Objektvariable oder With-Blockvariable nicht festgelegt" Ich hatte den Fehler schon mal, aber ich weiß nicht -.-" Ich bin echt am verzweifeln und muss den Code eigentlich bis um 9 fertig haben

Vielleicht noch kurz zur Erklärung, ich möchte, dass er in die Zelle eine "1" einfügt, die Fehlermeldung kam bei ##

Edit:
Ich hab das Range in WS3.Cells umbenannt, bei beidem kommt die gleiche Meldung...

Hallo s1rp4t!

Für Zeile 10 und 20 gilt für das Found2-Range-Object das Gleiche

Die Set-Variable Found entspricht bereits einer Range-Objekt-Adresse und bedarf keiner Zuweisung mittels WS3.Cells... oder WS3.Range...

Den Namen der Arbeitsmappe und des Tabellenblattes hast Du ja per Set-Anweisung der Variablen WS3 zugewiesen und bei der Set-Anweisung mit dem Find-Befehl:

Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)

wird der Variablen Found2 die komplette Adresse zugewiesen und heißt soviel wie: Set Found2 = WS3.Range(gefundene Zelle)

Also, z.B. nur "Found2.Offset(0, 5) = 2" schreiben.

Gruß Dieter

Vielen Dank, das Makro läuft bis zum 3. If-Tag, da meckert er wieder über das Problem vom Anfang, hier das If-Tag:

    If Not IsEmpty(WS1.Cells(i, 4)) Then
##       Set Found = WS2.Columns(1).Find(WS3.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
            Found2 = WS3.Columns(1).Find(Found.Offset(0, 1).Value, LookIn:=xlValues, LookAt:=xlWhole)
 
            If Found2 Is Nothing Then
     
                'WS3.Cells(i, 6) = 3  
       
            Else
         
            End If
        End If

"Objektvariable oder With-Blockvariable nicht festgelegt"

Hab das 3. IF-Tag mal kommentiert und den Code ausgeführt, allerdings passiert da nichts, hab mir dann in "WS1.Cells(i, 3) = Found" Found ausgeben lassen, die Zellen bleiben leer, hast du eine Idee wieso?

Edit:
Jetzt gibt er mir Found aus, allerdings soll Found ja eine Zelle sein und kein Wert, wird Found nun die Zelle oder der Wert der Zelle zugewiesen? Weil Found2 hat keinen Wert und das kann ja eigtl nciht sein, oder?

Hallo s1rp4t!

In Zeile 3 fehlt die Set-Anweisung: Set Found2

Meine letzte Antwort habe ich nochmal überarbeitet, also nochmal durchlesen.

Gruß Dieter

[edit]
Hab ich weiter oben doch schon geschrieben. Die Variable Found steht für ein Range-Object (Zell-Adresse).

Angenommen der Suchbegriff wurde in WS3 in Zelle A10 gefunden, dann steht Found für WS3.Range("A10") oder Found.Offset(0, 1) für WS3.Range("B10")
[/edit]

    If Not IsEmpty(WS1.Cells(i, 4)) Then
            Set Found = WS2.Columns(1).Find(WS3.Cells(i, 1), LookIn:=xlValues, LookAt:=xlWhole)
  ##     Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole)
 
            If Found2 Is Nothing Then
     
                WS3.Cells(i, 6) = 3
       
            Else
         
            End If
        End If

Next

Ich weiß nicht wieso, aber er hat das gleiche Problem... - Es würde aber auch ohne das 3. If-Tag gehen, wenn die anderen beiden funktionieren würden, denn ich könnte ja ausschließen. Hast du eine Idee wieso er für Found2 keinen Wert hat?

Liebe Grüße

Hallo s1rp4t!

Vermutlich, weil Found Nothing ist?

Du musst natürlich erstmal ermitteln, ob die Variable Found was gefunden hat. Bedeutet:

If Not Found Is Nothing Then 
    Set Found2 = WS3.Columns(1).Find(Found.Offset(0, 1), LookIn:=xlValues, LookAt:=xlWhole) 

    If Found2 Is Nothing Then WS3.Cells(i, 6) = 3
End If  

Gruß Dieter