Results 1 to 2 of 2
  1. #1
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480

    Trying to sort a sheet by a column, getting run time error. I think the range is the issue.


    So this code isn't working, I'm getting "Run-Time Error 1004 (Method 'Range' of object'_Global' failed)"

    Code:
    sh5.Columns("A:XFD").Sort Key1:=Range("M2"), Order1:=xlAscending, Header:=xlYes
    I'm fairly certain the issue is with the "M2", but I don't know what should go there.


    A little bit of information. sheet5 (sh5) could have 1-2000 rows, I'm wanting to sort the sheet by the column M. Any tips?

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Changing M2 to M1 might work.

    One technique I have used to work with Excel worksheets from Access VBA is to in Excel record a macro while you do what you need to do (in this case a sort), then take the resulting code and copy it into Access. It usually requires some modification, but not a great deal.

    For example, when I recorded a macro while sorting an Excel worksheet on a column, and answered yes at the prompt to expand the selection, the macro looked like this:

    Code:
    Sub Macro3()
    '
    ' Macro3 Macro
    '
    '
        Columns("B:B").Select
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B1"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange Range("A1:O25")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub
    The changes required to use it in access would be in referring to the worksheet.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-01-2013, 12:26 PM
  2. Replies: 1
    Last Post: 10-04-2012, 09:29 AM
  3. Sort by range of rows?
    By squirrly in forum Database Design
    Replies: 3
    Last Post: 08-22-2012, 01:16 PM
  4. Replies: 3
    Last Post: 08-15-2012, 04:15 PM
  5. Replies: 1
    Last Post: 07-13-2012, 07:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums