Consulting

Results 1 to 3 of 3

Thread: I'm having trouble setting a range in some worksheet code

  1. #1

    I'm having trouble setting a range in some worksheet code

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Maybe it should be

    Set sjI = Worksheets("Hovudlister").Range(Worksheets("Hovudlister").Range("C2"), Worksheets("Hovudlister").Range("C2").End(xlDown))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    That worked, thanks for the help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •