Suchbegriff bezieht sich nicht auf das angegebene Tabellenblatt

[Pergus]

Hallo Excel-Freunde,

ich bräuchte wieder mal Eure Hilfe in Excel-VBA.

In "Tabelle1" soll ein Suchbegriff in Zelle "C4" über ein Drop-Down ausgewählt werden.
In "Tabelle2" sind die relevanten Informationen dazu eingetragen, dessen Bezug zum Suchbegriff steht in der Spalte "E".

Nun soll das Makro, sobald sich in "Tabelle1" das Suchkriterium ändert, in "Tabelle2" nach diesem im angegebenen Bereich durchsuchen, doch in der Zeile

Code [Auswählen] Erweitern

Set rng = Worksheets("Tabelle2").Range(Cells(2, 5), Cells(letztezeile, 5)).Find(GewModule) bekomme ich die Meldung: Laufzeitfehler '1004': Anwendungs- und objektdefinierter Fehler.
Ich habe aber herausbekommen, dass das Suchkriterium in "Tabelle1" anstatt wie angegeben in "Tabelle2" gefunden wird, wieso weiß ich leider nicht.

Hier mein Code im Modul der "Tabelle1":

Code [Auswählen] Erweitern

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Suchbegriff As String
Dim letztezeile As Long
Dim rng As Range
Dim sFirstAddress As String
Dim GewModule As String
Dim m As Integer

If Target.Address <> "$C$4" Then Exit Sub

GewModule = Worksheets("Tabelle1").Range("C4").Value

letztezeile = Worksheets("Tabelle2").Cells(Rows.Count, 5).End(xlUp).Row
Set rng = Worksheets("Tabelle2").Range(Cells(2, 5), Cells(letztezeile, 5)).Find(GewModule)

If rng Is Nothing Then
  MsgBox "Kein " & GewModule & " gefunden!"
Else
  sFirstAddress = rng.Address
    Do
'Mein Makro...
    Set rng = Worksheets("Tabelle2").Range(Cells(2, 5), Cells(letztezeile, 5)).FindNext(GewModule)
    Loop While Not rng Is Nothing And rng.Address <> sFirstAddress
End If

Set rng = Nothing
Range("C4").Select

End Sub

Bin für jede Hilfe dankbar...

Lg aus Südtirol 

[C4RL0]

Hallo Pergus,

nur mal eben auf die Schnelle, ohne den Code komplett zu debuggen:
Wenn Du den Code im Klassenmodul von Tabelle 1 ausführst, enthält Deine "Cells"-Adresse implizit die Adresse von Tabelle1, welche es in Tabelle2 so natürlich nicht gibt.

Eine Möglichkeit, das zu umgehen, ware z.b. wie folgt:

Code [Auswählen] Erweitern

Set rng = Worksheets("Tabelle2").Range("E2:E" & letztezeile & "").Find(GewModule)

alternative definierst Du jeden "Cells"-Aufruf als "Worksheets("Tabelle2").Cells"