runtime error 1004

Colin Legg · #1 06-01-2012, 06:26 AM

Hi,

Please can you attach your workbook to the thread? Maybe you've got merged cells or something on the worksheet?

gbaker · #2 06-01-2012, 07:00 AM

Hi Colin,
I do have merged cells but they are above the area I want to sort. Workbook is very large. Let me try something first and then I'll send it to you if I can't figure it out.
Thanks

gbaker · #3 06-01-2012, 11:30 AM

I got rid of the merged cells and noticed that the freeze pane was also interferring with the sort. I am still getting another error called:
Runtime error '2147417848(800101108)

Debug line is .Apply (text is highlighted in red below in the code.

Code:

Sub SortDaysProvider()
    Dim DayRange As Long
    Dim TopRow As Long
    Dim sRange As Range
    Dim fRange As Range
 
    Application.ScreenUpdating = False
 
    For DayRange = 1 To 365
        TopRow = (DayRange * 17) + 9
 
        With ActiveWorkbook.Worksheets("Input Calendar (2012)")
 
            Set sRange = .Range("E" & TopRow & ":" & "BN" & TopRow + 14)
            Set fRange = .Range("E" & TopRow & ":" & "E" & TopRow + 14)
 
            With .Sort
                .SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal
                 .SetRange sRange
                 .Header = xlGuess
                 .MatchCase = False
                 .Orientation = xlTopToBottom
                 .SortMethod = xlPinYin
                 .Apply
                 .SortFields.Clear
            End With
        End With
    Next DayRange
 
    Application.ScreenUpdating = True
End Sub

Thanks in advance for all your help with this!!!

Colin Legg · #4 06-01-2012, 01:28 PM

That's an odd one. I used google and I found this thread. So, using the suggestion there:

Code:

            With .Sort
                .SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal
                 .SetRange sRange
                 .Header = xlGuess
                 .MatchCase = False
                 .Orientation = xlTopToBottom
                 .SortMethod = xlPinYin
                 Application.Calculate
                 .Apply
                 .SortFields.Clear
            End With

The person that had the problem on the other thread said this version failed on the first run, but after that it worked fine.

gbaker · #5 06-03-2012, 07:05 AM

Hi Colin,
I tried the new code a couple times and still get and error.
Stops at:

Code:

.SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal

Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Open Word w Excel & fill Word textboxes w info from Excel fields runtime error 4248	Joe Patrick	Word VBA	2	01-30-2012 07:23 AM
Runtime Error 4120 in Word 2007 macro	Frankwlc	Word	5	11-28-2011 01:54 AM
Runtime error 91	waldux	Word VBA	1	03-04-2011 11:25 PM
Runtime error 5487 - Word cannot complete the save to to file permission error	franferns	Word	0	11-25-2009 05:35 AM
Installing Access Runtime	ziggy0508	Office	1	09-29-2005 04:21 PM