schnufflchen
Jul 12, 2009, updated at 09:13:44 (UTC)
view91383
view23
0
Goto Top

Zelleninhalt per VBA in einer Spalte suchen

GermansolvedQuestionMicrosoft OfficeMicrosoft
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!
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 120279

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

Printed on: April 25, 2024 at 21:04 o'clock

23 Comments
Latest comment
Goto Top
Mitglied: 76109
76109 Jul 12, 2009 at 10:13:41 (UTC)
Goto Top
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]
Member: Schnufflchen
Schnufflchen Jul 12, 2009 at 14:55:59 (UTC)
Goto Top
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?
Mitglied: 76109
76109 Jul 12, 2009 at 15:13:11 (UTC)
Goto Top
Hallo Schnufflchen!

Ups, daran hatte ich nicht gedacht face-smile Code oben geändert.

Gruß Dieter
Member: Schnufflchen
Schnufflchen Jul 12, 2009 at 15:37:51 (UTC)
Goto Top
Herzlichen Dank did1954,
du hast mir sooooo geholfen! Jetzt kann ich wieder weiterarbeiten!
Mitglied: 76109
76109 Jul 12, 2009 at 15:52:12 (UTC)
Goto Top
Hallo Schnufflchen!

Gern Geschehenface-smile

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
Member: s1rp4t
s1rp4t Jul 02, 2010 at 16:15:33 (UTC)
Goto Top
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
Mitglied: 76109
76109 Jul 02, 2010 at 16:47:13 (UTC)
Goto Top
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)
Member: s1rp4t
s1rp4t Jul 05, 2010 at 06:54:20 (UTC)
Goto Top
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 ##
Mitglied: 76109
76109 Jul 05, 2010 at 08:15:13 (UTC)
Goto Top
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
Member: s1rp4t
s1rp4t Jul 05, 2010 at 08:24:57 (UTC)
Goto Top
Vielen Dank, ich werde das bis zum Mittag mal ausprobieren und mich bei Bedarf noch mal melden.

Liebe Grüße
Member: s1rp4t
s1rp4t Jul 05, 2010 at 13:57:43 (UTC)
Goto Top
So ich habe jetzt noch einen draufgesetzt (man muss sich ja steigern face-wink)

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]
Member: s1rp4t
s1rp4t Jul 05, 2010 at 14:09:20 (UTC)
Goto Top
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
Mitglied: 76109
76109 Jul 05, 2010 at 19:22:55 (UTC)
Goto Top
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 hastface-wink

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 face-smile [/edit]
Member: s1rp4t
s1rp4t Jul 06, 2010 at 06:47:09 (UTC)
Goto Top
Nochmals Danke face-smile


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 face-sad
Member: s1rp4t
s1rp4t Jul 06, 2010 at 06:51:31 (UTC)
Goto Top
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...
Mitglied: 76109
76109 Jul 06, 2010 at 07:14:34 (UTC)
Goto Top
Hallo s1rp4t!

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

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
Member: s1rp4t
s1rp4t Jul 06, 2010 at 07:21:01 (UTC)
Goto Top
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"
Member: s1rp4t
s1rp4t Jul 06, 2010 at 07:27:22 (UTC)
Goto Top
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?
Mitglied: 76109
76109 Jul 06, 2010 at 07:29:14 (UTC)
Goto Top
Hallo s1rp4t!

In Zeile 3 fehlt die Set-Anweisung: Set Found2face-wink

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]
Member: s1rp4t
s1rp4t Jul 06, 2010 at 07:35:30 (UTC)
Goto Top
    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
Mitglied: 76109
76109 Jul 06, 2010 at 07:44:49 (UTC)
Goto Top
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
Member: s1rp4t
s1rp4t Jul 06, 2010 at 08:05:45 (UTC)
Goto Top
Tausend Dank, es hat alles geklappt =)
Mitglied: 76109
76109 Jul 06, 2010 at 08:31:43 (UTC)
Goto Top
Hallo s1rp4t!

Gern geschehen, freut mich, wenn's nun funktioniertface-smile

Gruß Dieter
GermansolvedQuestionMicrosoft OfficeMicrosoft
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 5 Comments