Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-29-2012, 07:32 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default runtime error 1004


I am getting a error when I try to use the macro attached. Can you help!!!
Attached Files
File Type: docx Sub SortDaysProvider.docx (13.1 KB, 2 views)
Reply With Quote
  #2  
Old 05-31-2012, 09:20 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

There's a limit of 64 sortfields to a sort which is one of several reasons why you might get that error (you're trying to create 365 sortfields). I've guessed at what you want to do and tidied your code to the below. If it's not what you want please can you describe in simple words what you want your code to do?

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
Reply With Quote
  #3  
Old 06-01-2012, 05:57 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Runtime error 1004

Hi Colin,
Thanks for the new code. I am still getting a runtime error 1004. Debug shows highlighted as the source of the error,

Code:
.SortFields.Add _
                     Key:=fRange, _
                     SortOn:=xlSortOnValues, _
                     Order:=xlAscending, _
                     DataOption:=xlSortNormal
Any idea's
Thanks in advance for all your help!!!
Reply With Quote
  #4  
Old 06-01-2012, 06:26 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi,

Please can you attach your workbook to the thread? Maybe you've got merged cells or something on the worksheet?
Reply With Quote
  #5  
Old 06-01-2012, 07:00 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default

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
Reply With Quote
  #6  
Old 06-01-2012, 11:30 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Runtime error '2147417848(800101108)

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!!!
Reply With Quote
  #7  
Old 06-01-2012, 01:28 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #8  
Old 06-03-2012, 07:05 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Runtime 1004

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
Reply With Quote
  #9  
Old 06-03-2012, 09:48 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi,

Please attach your workbook to the thread so I can have a go at debugging it?
Reply With Quote
  #10  
Old 06-03-2012, 10:09 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Run time error 1004

Hi Colin,
I attached a sheet so you can Debug. Thanks
I made is smaller to be able to send it. If you find out the problem let me know so I can adapt it to the Master Workbook which is over 6 meg.
Thanks in advance.
GWB
Attached Files
File Type: xlsm TEST SHEET.xlsm (190.6 KB, 1 views)
Reply With Quote
  #11  
Old 06-05-2012, 02:54 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Hi,

I don't get an error when I run the SortDaysProvider() sub in the test workbook you attached. Please would you confirm that you definitely get a runtime error in that workbook?
Reply With Quote
  #12  
Old 06-06-2012, 05:23 AM
gbaker gbaker is offline runtime error 1004 Windows 7 32bit runtime error 1004 Office 2010 32bit
Competent Performer
runtime error 1004
 
Join Date: May 2012
Posts: 111
gbaker is on a distinguished road
Default Runtime 1004

I tried it and although I didn't get an error this time, it doesn't sort the way I need it to.
Need to sort in alphabetical order column C through colum T just want to sort 15 lines under the date for each day I don't get an error but it doesn't sort Don't have enough experience to figure it out

I attached another sheet for you to look at.
Thanks in advance for all your help!!!

Also had to get rid of Feb through Dec because the file was 1.7 meg, too large to send.
Attached Files
File Type: xlsm TEST SHEET 2.xlsm (120.6 KB, 0 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
runtime error 1004 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 1004 Runtime Error 4120 in Word 2007 macro Frankwlc Word 5 11-28-2011 01:54 AM
runtime error 1004 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 07:30 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft