Laufzeitfehler 93

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Private Sub TextBox2_Change() Dim rng As Range Dim sAddress As String Dim wksrng As Range Dim saAddress As String Dim wks As Worksheet ' zuerst Variablen deklarieren, dann Code... ListBox1.Clear ListBox2.Clear If TextBox2 = "" Or TextBox2 = " " Or Len(TextBox2)

'... If Not wksrng Is Nothing Then saAddress = wksrng.Address Do With ListBox2 .ColumnCount = 2 .AddItem wksrng.Text .List(.ListCount - 1, 1) = wks.Range("B1") End With ' ListBox2.AddItem wksrng.Text Set wksrng = Cells.FindNext(after:=wksrng) Loop Until wksrng.Address = sAddress '...

Option Explicit Public Kategorie As String Private Sub TextBox2_Change() Dim rng As Range Dim sAddress As String Dim wksrng As Range Dim saAddress As String Dim wks As Worksheet ListBox1.Clear ListBox2.Clear If TextBox2 = "" Or TextBox2 = " " Or Len(TextBox2)

Hallo,
böse_Falle_Part_II: Du hast nicht nur die Stringvariable aus dem ersten Loop verwendet, sondern _ referenzierst auch auf Sheets(Kategorie) aus dem ersten Loop bei Deinem FindNext-Aufruf im zweiten Loop, Du mußt auf die Laufvariable wks Deines For-Each-Loops referenzieren:


'... Set Rng = Nothing
                  For Each wks In Worksheets
                      Set wksrng = wks.Cells.Find(what:=TextBox2.Text, after:=ActiveCell, _
LookIn:=xlFormulas, LookAt:= _
                      xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
_
                      , SearchFormat:=False)
                         If Not wksrng Is Nothing Then
                         saAddress = wksrng.Address
                              Do
                                   With ListBox2
                                  .ColumnCount = 2
                                  .AddItem wksrng.Text
                                  .List(.ListCount - 1, 1) = wks.Range("B1")
                                   End With
                                   Set wksrng = wks.Cells.FindNext(after:=wksrng)
                              Loop Until wksrng.Address = saAddress
                        End If
    Next wks
'...

Gruß, Mullit

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Laufzeitfehler 93

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread