Why is the .select method failing?

    Whenever I run this macro, I'm told that the select method of the range class failed. I know I'm missing something obvious. What I'm really trying to do is select the range just below this cell, like this:


    Code
    Range(cell.Offset(-1, 0), cell.End(xlUp)).Select


    Thank you in advance.


    Jason


    Re: Why is the .select method failing?


    Hi,


    Does it actually find "Ord Time".
    [vba]If not cell is nothing then
    cell.Offset(-1, 0).Select
    else
    msgbox "nothing found"
    end if[/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: Why is the .select method failing?


    Yes, it does. If I comment out the latter part of the code and type


    msgbox cell


    it returns "Ord Time".

    Re: Why is the .select method failing?


    You ar probably mixing sheet references then, try this[vba]Worksheets(wsnum).Range(cell.Offset(-1, 0), cell.End(xlUp)).Select[/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: Why is the .select method failing?


    Quote

    What I'm really trying to do is select the range just below this cell, like this:


    Code
    Range(cell.Offset(-1, 0), cell.End(xlUp)).Select


    Offset(-1,0) selects the cell ABOVE cell. Try Offset (1,0) to go below.


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Re: Why is the .select method failing?


    Try changing

    Code
    cell.Offset(1, 0).Select


    to read

    Code
    cell.Worksheet.Activate
cell.Offset(1, 0).Select


    Hope this helps

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Re: Why is the .select method failing?


    Hmm, there must be something more fundamental going wrong here. I just simplified my code to the following, and it still gives me the error, even though I know this test code was working before I got the error the first time.


    Is there some way I could have inadvertantly turned off a set of methods or something?


    Re: Why is the .select method failing?


    Where does it fail now?


    Alastair

    Einstein:
    Things should be made as simple as possible, but not any simpler


    Be sure to check out TemplateZone for all your Microsoft Office Needs.
    Get OfficeReady Professional 3.0 here!

    Re: Why is the .select method failing?


    You still don't seem to be activating the worksheet before trying to select the cell - you're trying to do it all in one command which won't work.


    I would have thought that if you follow Barrie's advice above it would work.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

    Re: Why is the .select method failing?


    Sorry to be such a pain, but it still fails when I use Barrie's method. It fails at "cell.Worksheet.Activate":


    Re: Why is the .select method failing?


    The sample workbook you posted didn't contain "Ord Time" in any worksheet. That's why you can't select it - it doesn't exist.

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

    Re: Why is the .select method failing?


    Argh! I'm sorry about that. At some point during my testing I accidentally assigned a different value to the cell that originally contained the text.


    Thank you.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login