View Full Version : [SOLVED] I'm having trouble setting a range in some worksheet code

11-19-2013, 02:09 AM
I was wondering if someone please could explain to me why I fail to set the range "sjI" in the code below. More specifically I get the error message "Run-time error '1004'. Method 'Range' of object '_Worksheet' failed." For the life of me I can't understand what it is I am doing wrong.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As String, sjI As Range

If Not Intersect(Target, ActiveSheet.Range("B3:S21")) Is Nothing Then
If Not IsEmpty(Target) And Target.Count = 1 Then
s = Right(Target, Len(Target) - 2)
s = Replace(s, vbLf, " ")
s = Replace(s, "-", "")
Set sjI = Range(Worksheets("Hovudlister").Range("C2"), Worksheets("Hovudlister").Range("C2").End(xlDown))
Debug.Print (sjI.Address)
End If
End If
End Sub

Bob Phillips
11-19-2013, 02:56 AM
Maybe it should be

Set sjI = Worksheets("Hovudlister").Range(Worksheets("Hovudlister").Range("C2"), Worksheets("Hovudlister").Range("C2").End(xlDown))

11-19-2013, 02:59 AM
That worked, thanks for the help :)