Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programed sort generates error: originated as recorded macro
Hello Excel Gurus,
I have the function command to perform a sort: Private Function SortRows() Dim i As Integer i = 1 With Worksheets("Sheet2") For i = 1 To 20 Debug.Print .Range("A" & i).Value If IsEmpty(.Range("A" & i).Value) = True Then Do While IsEmpty(.Range("A" & i).Value) = True .Rows(i & ":" & i).Delete Shift:=xlUp Loop .Range("A20").Value = "END" End If Next i i = 1 Do While .Range("A" & i).Value < "END" i = i + 1 Loop i = i .Range("A1:J" & i - 1).Select ..Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending End With End Function When it hits the line ..Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending I get the error #1004: "The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank." The command is a simplified version of a recorded macro that generated the same error. I think the problem may have to do with the fact that I have been deleting rows prior the issuance of the command. Is that correct? If so how do I fix it? Thank You, -plh -- Where are we going and why am I in this HAND BASKET?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programed sort generates error: originated as recorded macro
You qualified lots of your ranges, but missed them in this line:
.Range("A1:J" & i - 1).Sort _ Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending .Range("A1:J" & i - 1).Sort _ Key1:=.Range("A1"), Order1:=xlAscending, _ Key2:=.Range("B1"), Order2:=xlAscending (those dots in the key# parms are important) plh wrote: Hello Excel Gurus, I have the function command to perform a sort: Private Function SortRows() Dim i As Integer i = 1 With Worksheets("Sheet2") For i = 1 To 20 Debug.Print .Range("A" & i).Value If IsEmpty(.Range("A" & i).Value) = True Then Do While IsEmpty(.Range("A" & i).Value) = True .Rows(i & ":" & i).Delete Shift:=xlUp Loop .Range("A20").Value = "END" End If Next i i = 1 Do While .Range("A" & i).Value < "END" i = i + 1 Loop i = i .Range("A1:J" & i - 1).Select .Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending End With End Function When it hits the line .Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending I get the error #1004: "The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank." The command is a simplified version of a recorded macro that generated the same error. I think the problem may have to do with the fact that I have been deleting rows prior the issuance of the command. Is that correct? If so how do I fix it? Thank You, -plh -- Where are we going and why am I in this HAND BASKET?? -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programed sort generates error: originated as recorded macro
Works swimmingly now, thank you!
-plh In article , Dave Peterson says... You qualified lots of your ranges, but missed them in this line: .Range("A1:J" & i - 1).Sort _ Key1:=Range("A1"), Order1:=xlAscending, _ Key2:=Range("B1"), Order2:=xlAscending .Range("A1:J" & i - 1).Sort _ Key1:=.Range("A1"), Order1:=xlAscending, _ Key2:=.Range("B1"), Order2:=xlAscending (those dots in the key# parms are important) plh wrote: Hello Excel Gurus, I have the function command to perform a sort: Private Function SortRows() Dim i As Integer i = 1 With Worksheets("Sheet2") For i = 1 To 20 Debug.Print .Range("A" & i).Value If IsEmpty(.Range("A" & i).Value) = True Then Do While IsEmpty(.Range("A" & i).Value) = True .Rows(i & ":" & i).Delete Shift:=xlUp Loop .Range("A20").Value = "END" End If Next i i = 1 Do While .Range("A" & i).Value < "END" i = i + 1 Loop i = i .Range("A1:J" & i - 1).Select .Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending End With End Function When it hits the line .Range("A1:J" & i - 1).Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1"), Order2:=xlAscending I get the error #1004: "The sort reference is not valid. Make sure it's within the data you want to sort, and the first Sort By box isn't the same or blank." The command is a simplified version of a recorded macro that generated the same error. I think the problem may have to do with the fact that I have been deleting rows prior the issuance of the command. Is that correct? If so how do I fix it? Thank You, -plh -- Where are we going and why am I in this HAND BASKET?? -- Where are we going and why am I in this HAND BASKET?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003 macro generates compiler error on 2007 | Excel Worksheet Functions | |||
VB Code generates error on another machine? | Excel Programming | |||
Working application - Now generates error | Excel Programming | |||
Recorded Macro to Set page breaks generates error. | Excel Programming | |||
Find generates error message | Excel Programming |