PDA

View Full Version : Range Problem



HokusPokus
05-01-2018, 08:22 AM
Hallo zusammen,

bekomme immer die Fehlermeldung 424 (Objekt erforderlich) weis nicht warum :/
Ziel ist es einen bereich auf Inhalt zu prüfen.



Sub Start()

dim Bereich as Range

Set Bereich = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 5)).Address <--- Hier kommt der Fehler.

If Bereich Is Nothing Then
'TU DIES'
Else
'DU DAS'
End If




Besten Dank :)

SamT
05-01-2018, 08:47 AM
Set Bereich = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 5)).Address <--- Hier kommt der Fehler.

Set Bereich = Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 5))

"Set" is for Objects. "Address" is a String

Maschinelle Übersetzung:
"Set" ist für Objekte. "Adresse" ist ein String

HokusPokus
05-01-2018, 08:57 AM
Hey SamT,

thanks for your answer, but i have this problem with your idea too, it comes the same ERROR 424.

SamT
05-01-2018, 10:27 AM
Help File says..."[ActiveCell] Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails. Read-only... If you don't specify an object qualifier, this property returns the active cell in the active window." (Emph by me)


Try

Sheets("SheetName").Activate
Set Bereich = Application.ActiveCell.Offset(0, 1).Resize(1,4)

Too bad you can't use "Selection." That's very easy

Set Bereich = Selection.Offset(0, 1).Resize(1,4)

Troubleshooting:

MsgBox ActiveWindow.Caption 'Should be Workbook name
MsgBox ActiveCell.Parent.Name 'Sheet name
MsgBox ActiveCell.Address

Worst Case

Dim ACAdd As String

Sheets("SheetName").Activate
ACAdd = ActiveCell.Address

Set Bereich = Sheets("SheetName").Range(ACAdd).Offset(0, 1).Resize(1,4)

James72
08-19-2019, 10:33 PM
I am also facing the same error 424 while implementing the code. Help me overcome my problem please
Regards