Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Method of Range Class Failed (97 SR-2)

    Hi

    I'm trying to insert values into cells on a different worksheet (in the same workbook), and no matter whether I use the copy/paste method or the .value property, I get the same error - '1004 - Select Method of Range Class Failed'.

    What am I doing wrong!!?? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Hi,
    Can you post the actual code you're using?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Hi

    It's:

    Worksheets("X").Select
    Range("B3").End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    ActiveCell.Paste


    Be gentle with me as I'm not great on Excel!!

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Is that the entire code? I can't see anything inherently wrong with that as you've posted it (it certainly runs OK on my system). However, selecting cells and copying and pasting are not usually the fastest methods of copying values from one sheet to another so if you can provide more detail we can try and come up with something that works for you.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Rory

    The entire code is as follows (most of I'm sure not relevant to the problem in hand, however there may be something there that is causing this:

    Private Sub cmdApply_Colour_Click()


    On Error GoTo cmdApply_Colour_Click_Err


    intLength = 0

    Application.ScreenUpdating = False

    ActiveSheet.Unprotect

    Range("B3").End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select


    For Each cell In Selection

    Select Case cell.Value

    Case Is = "BB"
    cell.Interior.Color = RGB(0, 204, 255) 'Blue

    Case Is = "KP"
    cell.Interior.Color = RGB(255, 153, 0) 'Orange

    Case Is = "OS"
    cell.Interior.Color = RGB(204, 51, 0) ' Brown

    Case Is = "PB"
    cell.Interior.Color = RGB(255, 0, 0) ' Red

    Case Is = "RN"
    cell.Interior.Color = RGB(255, 0, 255) 'Purple

    Case Is = "TR"
    cell.Interior.Color = RGB(192, 192, 192) 'Grey

    Case Is = "TP"
    cell.Interior.Color = RGB(255, 255, 0) 'Yellow

    Case Is = "YT"
    cell.Interior.Color = RGB(153, 204, 0) 'Green

    Case Is = "NF"
    intLength = intLength + 1

    Case Else

    End Select

    Next

    Range("AT3").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = intLength


    Range("AS2").End(xlDown).Offset(1, 0).Select
    ActiveCell.Value = Now()

    Range("B3").End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Copy
    Range(ActiveCell, ActiveCell.End(xlToRight)).Locked = True

    Worksheets("Trailers_Complete").Select
    Range("B3").End(xlDown).Select
    Range(ActiveCell, ActiveCell.End(xlToRight)).Select
    ActiveCell.Paste

    Worksheets("Trailers").Protect

    'GetNumbers

    Exit Sub

    cmdApply_Colour_Click_Err:

    MsgBox Err.Number & " " & Err.Description
    Application.ScreenUpdating = True

    End Sub

    I'm calling this sub from a button on the main worksheet.

    Thanks

    Adrian

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Is there definitely data below cells AT3 and AS2? Your code is doing the equivalent of selecting those cells, pressing Ctrl+Down arrow and then trying to select a cell one row below that, so if there isn't any data below one of those cells, you're actually trying to select a cell below the bottom of the worksheet!
    If that's not the case, try commenting out your On Error GoTo cmdApply_Colour_Click_Err statement and then when the error occurs, choose debug to go to the offending line of code, which should help to narrow down the problem.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Method of Range Class Failed (97 SR-2)

    Also, you need Application.ScreenUpdating = True before the Exit Sub, but that has nothing to do with the error. Could the copy and paste areas be different sizes? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Guys

    Thanks for the help so far. The xlDown thing is fine as the column has already been 'seeded' with data.

    I removed the error handlers and the line causing the problem seems to be the one where the range is selected:

    Range("B3").End(xlDown).Select

    I've tried to reference this differently (i.e. using the Cells (X,Y) method and With...End With, but I get exactly the same error every time.

    Help
    <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

  9. #9
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Method of Range Class Failed (97 SR-2)

    We'll probably have to see the workbook. Can you make a small one and remove the color stuff from the macro. Make sure it still bombs in the same place! Thanks!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  10. #10
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    It's pretty small so I'll send the whole file anyway. I've left the colour thing in just in case that it causing the problem.

    Thanks
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Method of Range Class Failed (97 SR-2)

    Adrian, since your code is in a sheet, all Range references refer to that sheet instead of the activesheet like you are used to. Replace the Worksheet().Select down to the paste with:<pre>Dim wsTC As Worksheet
    Set wsTC = Worksheets("Trailers_Complete")
    wsTC.Select
    wsTC.Range("B3").End(xlDown).Paste</pre>

    I think that fixes it. When you are writing code for a worksheet, it is a good idea to put a worksheet reference before every Range, Cell, etc. reference. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Sammy

    Thanks for your help. I tried your method and I'm still getting error messages at the point where it tries to paste. Did you get it to work when you tried it?

    The only difference that I can see compared to the code snippet you attached is that I need to offset by 1 row the (to reach the first blank row) before I paste.

    Thanks again for your help

  13. #13
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Select Method of Range Class Failed (97 SR-2)

    Duh, I thought it worked, but it dosn't for me either. See new code it the attachment; hopefully it does what you want. Sorry! --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  14. #14
    Lounger
    Join Date
    May 2001
    Location
    Engerland
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Method of Range Class Failed (97 SR-2)

    Sam

    Worked a treat - thanks for your help <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

Posting Permissions

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