Results 1 to 5 of 5

Thread: [RESOLVED] Runtime Error 1004 Sorting in Excel from VB6

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    5

    Resolved [RESOLVED] Runtime Error 1004 Sorting in Excel from VB6

    I.m building an application where I transfer a series of data to an excel workshhet, in 5 columns (12,000+ rows) I need to sort the whole range, but I get a "Run-time error '1004'. Method 'Range' of object '_Global' failed, when setting the range. the code is:
    .....
    Temp = "A2:F" & Trim(Str$(RecRecs + 2)) ' RecRecs is the number of rows
    oSheet.Sort.SortFields.Clear ' Clear as need to do 4 different sorts (2 keys on each)
    oSheet.Sort.SetRange Range(Temp) ' This is the instruction that causes the 1004 error
    oSheet.Range(Temp).Sort.Header = xlYes ' Header row is row 2. The 2 keys in the next lines
    oSheet.Sort.SortFields.Add Key:=Range("B2:B12093"), SortOn:=xlSortOnValues, Order:=xlAscending
    oSheet.Sort.SortFields.Add Key:=Range("C2:C12093"), SortOn:=xlSortOnValues, Order:=xlAscending
    oSheet.Sort.Apply
    .....
    Code continues ....

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,965

    Re: Runtime Error 1004 Sorting in Excel from VB6

    try using the SET keyword on the first line, as it is, temp is probably a 2d array rather than a range object
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3
    gibra
    Guest

    Re: Runtime Error 1004 Sorting in Excel from VB6

    Sort require a Range object, in this line:
    Code:
    oSheet.Sort.SetRange Range(Temp) ' This is the instruction that causes the 1004 error

    Range object could not be created implicitly.

    Try
    Code:
    oSheet.Sort.SetRange oSheet.Range(Temp)
    Or create a Range object,first.

  4. #4
    Frenzied Member
    Join Date
    Mar 2008
    Posts
    1,176

    Re: Runtime Error 1004 Sorting in Excel from VB6

    Try structuring your code so that it reads more easily...The following is just an example which you could adapt to meet your needs
    Code:
             
        With .Sheets(1)
             Dim rngSortRange As Range, rngSortKey As Range
             Set rngSortRange = .Range(.Cells(FirstRow, 1), .Cells(R, LastCol))
             Set rngSortKey = .Range(.Cells(FirstRow + 1, 2), .Cells(R, 2))
             With .Sort
                 .SortFields.Clear
                 .SortFields.Add Key:=rngSortKey _
                     , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                 .SetRange rngSortRange
                 .Header = xlYes
                 .MatchCase = False
                 .Orientation = xlTopToBottom
                 .SortMethod = xlPinYin
                 .Apply
             End With
             Set rngSortRange = Nothing
             Set rngSortKey = Nothing
        End With
    and it turns into something you can pretty well copy and paste next time you need it, or turn into a general purpose Sub.

  5. #5

    Thread Starter
    New Member
    Join Date
    Feb 2015
    Posts
    5

    Re: Runtime Error 1004 Sorting in Excel from VB6

    Thank You Magic Ink. I combined the code to the following and I'm considering turning it into a Sub, as I do 4 sorts in sequence. 3 use 2 keys while 1 uses only 1, I'm thinking of conditioning the second AddKey instruction to execute if the KeySrt2 variable is "not empty"

    Temp = "A2:G" & Trim(Str$(RecRecs + 2))
    oSheet.Sort.SortFields.Clear ' This instruction works
    KeySrt1 = "G3:G" & Trim(Str$(RecRecs + 2))
    KeySrt2 = "F3:F" & Trim(Str$(RecRecs + 2))
    oSheet.Sort.SortFields.Add Key:=oSheet.Range(KeySrt1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    oSheet.Sort.SortFields.Add Key:=oSheet.Range(KeySrt2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With oSheet.Sort
    .SetRange oSheet.Range(Temp)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Last edited by Canalbin; Feb 28th, 2015 at 06:59 PM. Reason: crowded appearance, hard to read

Posting Permissions

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



Click Here to Expand Forum to Full Width