Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Error When Sorting
We have a large macro. In one of the sub's we sort a
worksheet that has nine columns. The first three colomns are text and the last six are numeric. For each row there is only one numeric entry in column 4, 5, 6, 7, 8, or 9. In the sub-routine below we are sorting column 4, then column 5, then column 6, etc. The problem is that the macro bombs if, for example, there is no entry in column 5. The error occurs on the 6th line of code, "If cells(1, MyColumn)....." It seems like the End(xlDown) is identifying row 65536 and therefore cannot look at Offset (1,0). Do we need to insert code before line 6 to test for this condition somehow? --------------------------- Range("A2").Select 'Sort the worksheet on column D, the third column. Range(Selection, ActiveCell.SpecialCells _ (xlLastCell)).Select Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom 'Sort the worksheet based on columns 4 through 9. On Error Resume Next For MyColumn = 4 To 8 If Cells(1, MyColumn).End(xlDown).Offset(1, 0) _ = "" Then Set NewStCell = Cells(1, MyColumn).End _ (xlDown).Offset(1, -MyColumn + 1) Else Set NewStCell = Cells(1, MyColumn).End _ (xlDown).End(xlDown).Offset(1, -MyColumn + 1) End If Set RangeToSort = Range(NewStCell, _ Cells(NewStCell.End(xlDown).Row, 9)) RangeToSort.Sort Key1:=Cells(1, MyColumn + 1), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom Next MyColumn |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
VBA Sorting Error | Excel Worksheet Functions | |||
sorting error | Excel Discussion (Misc queries) | |||
sorting error | Excel Worksheet Functions |