Ich finde den Fehler nicht

[Usedom]

Guten Abend.

Mich macht zur Zeit ein Fehler wahnsinnig, dessen Ursache ich nicht finde. In einer Prozedur soll in der Tabelle "Personal" ein Bereich festgelegt werden, in dem eine Suche stattfindet. Der Bereich wird definiert in der Spalte 14 von Zeile 2 bis zur letzten Zeile der Tabelle. Folgender Code (Auszug):

Dim lngLZ As Long
Dim rngBereich As Range
lngLZ = Worksheets("Personal").Cells(Rows.Count, 1).End(xlUp).Row
Set rngBereich = Worksheets("Personal").Range(Cells(2, 14), Cells(lngLZ, 14))

Und bei der letzten Zeile des Codes (farbig) steigt Excel aus mit der Meldung:

"Laufzeitfehler '1004': Anwendungs- oder objektdefinierter Fehler"

Was mache ich hier falsch?

[Steffl]

Hallo,

hänge mal das Worksheet noch vor dem Cells an

Code:

Dim lngLZ As Long
Dim rngBereich As Range
lngLZ = Worksheets("Personal").Cells(Rows.Count, 1).End(xlUp).Row
Set rngBereich = Worksheets("Personal").Range(Worksheets("Personal").Cells(2, 14), Worksheets("Personal").Cells(lngLZ, 14))

[Usedom]

Hallo Stefan.

Das ist die Lösung! Ich hatte mittlerweile herausgefunden, dass der Fehler nicht auftritt, wenn ich vor der Zeile "Set rngBereich..." folgende Zeile einfüge: "Worksheets("Personal").Activate" oder statt dessen vor der Ausführung des Codes das Blatt "Personal" manuell aktiviert habe. Das heißt, dass mein Code

Set rngBereich = Worksheets("Personal").Range(Cells(2, 14), Cells(lngLZ, 14))

die Zellen des Blattes "Personal" nicht ausreichend referenziert (sollte man nicht meinen), und dass es aber trotzdem funktioniert, wenn das Blatt "Personal" aktiv ist.

Vielen Dank. Wieder etwas gelernt.

[Gast 123]

Hallo Usedom

der Code von Stefan ist richtig.  Ich benutze aber lieber eine andere Schreibweise, mag nicht Ellenlange Syntax.
In solchen Faellen nehme ich eine separete End-Adresse, oder benutze Resize  (bestehenden Bereich erweitern)

mfg  gAst 123

Code:

'Variante über extra End-Adresse
EndAdr = Worksheets("Personal").Cells(lngLZ, 14).Address
Set rngBereich = Worksheets("Personal").Range(Cells(2, 14), EndAdr)

'Variante über Resize(lngLZ, 1)
Set rngBereich = Worksheets("Personal").Range(Cells(2, 14)).Resize(lngLZ, 1)

[Usedom]

Guten Abend gAst123.

Deinen Vorschlag werde ich morgen testen. Ich werde den aber mit der Lösung von Stefan kombinieren müssen, denn in deinem Vorschlag ist die Referenzierung wohl auch nicht vollständig, würde also wohl ebenfalls voraussetzen, das das Blatt "Personal" aktiv ist.

[Winny]

Das geht schief. 

Set rngBereich = Worksheets("Personal").Range(Cells(2, 14), EndAdr)

Wenn das aktive Blatt ein anderes als "Personal" ist, dann referenziert Cells(2,14) diesen Bereich was zu einem Fehler führt. Ich bevorzuge

Code:

  With Worksheets("Personal")
     lngLZ = .Cells(.Rows.Count, 1).End(xlUp).Row
     Set rngBereich = Range(.Cells(2, 14), .Cells(lngLZ, 14))
  End With

[Usedom]

Das ist die Lösung von Stefan, verpackt in eine With-Anweisung. Funktioniert.

[Gast 123]

Hallo Winny,

ich programmiere seit >20 Jahren und stolpere immer wieder über dumme Flüchtigkeitsfehler.
Du warst sehr aufmerksam, und hast gleichzeitig eine gute Lösung angeboten.    Respekt!!

mfg  Gast 123

[snb]

oder

Code:

sub M_snb()
  Sheets("Personal").columns(14).specialcells(2).offset(1).specialcells(2)="so geht's auch"

  Sheets("Personal").usedrange.columns(14).offset(1).specialcells(2)="Alternative 1"

  Sheets("Personal").cells(1).currentregion.columns(14).offset(1).specialcells(2)="Alternative 2"
End Sub