#1
|
|||
|
|||
runtime error 1004
I am getting a error when I try to use the macro attached. Can you help!!!
|
#2
|
||||
|
||||
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 |
#3
|
|||
|
|||
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 Thanks in advance for all your help!!! |
#4
|
||||
|
||||
Hi,
Please can you attach your workbook to the thread? Maybe you've got merged cells or something on the worksheet? |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
||||
|
||||
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 |
#8
|
|||
|
|||
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 |
#9
|
||||
|
||||
Hi,
Please attach your workbook to the thread so I can have a go at debugging it? |
#10
|
|||
|
|||
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 |
#11
|
||||
|
||||
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? |
#12
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
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 |