Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort & Subtotalling isn't working?
Hi all
I have parts of a spreadsheet that I'm trying to format. This includes sorting cells "A22:FinalRow" based on column L (this column carries Alpha data but not in all cells) and then subtotalling most of the columns from and based on column L again. I also need just the subtotals to show on the spreadsheet. The sorting and subtotalling is not happening when I run the code I attempted (sorry - novice warning here)? I'm not getting any error on the sorting - just no result?? The Subtotalling is bringing up an error "not finding a list". When I select just cell "L22" it only sorts 4 columns because others are blank, therefore I have to nominate the range of A22:FinalRow? Not sure why this isn't working? The error message I'm getting is: "Run-Time Error '1004': "No list was found. Select a single cell within your list, and then click the command again" Everything else works great?? I've highlighted the problem area with "[" in the margin. Any help would be greatly appreciated. Sub Thousands90day() FinalRow = Cells(65536, "DR").End(xlUp).Row For i = 13 To FinalRow + 1 Cells(i, "A").Resize(, 131).Interior.ColorIndex = xlNone Next i For i = 22 To FinalRow Cells(i, "AC").Resize(, 91).FormulaR1C1 = txt1 Cells(i, "AC").Resize(, 91).Copy Cells(i, "AC").Resize(, 91).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Cells(i, "AC").Resize(, 91).NumberFormat = "#,##0.00" Cells(i, "AC").Resize(, 91).Font.Size = 10 Cells(i, "AC").Resize(, 91).Replace What:="0", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Application.DisplayAlerts = False [ Cells(i, "A").Resize(, 130).Sort Key1:=Range("L22"), Order1:=xlAscending, [ Header:=xlGuess, _ [ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ [ DataOption1:=xlSortNormal [ Cells(i, "L").Resize(, 119).Subtotal GroupBy:=12, Function:=xlSum, [ TotalList:=Array(29, 30, _ [ 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, [ 50, 51, 52, 53, 54, 55, 56, _ [ 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, [ 76, 77, 78, 79, 80, 81, 82, _ [ 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, [ 101, 102, 103, 104, 105, 106, _ [ 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 121, [ 122, 129, 130), Replace _ [ :=True, PageBreaks:=False, SummaryBelowData:=True [ Next i [ Application.DisplayAlerts = True Range("AB15").Select .... -- Thank heaps for your time and help BeSmart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort not working | Excel Worksheet Functions | |||
Subtotalling | New Users to Excel | |||
Subtotalling | Excel Worksheet Functions | |||
Subtotalling | Excel Worksheet Functions | |||
data subtotalling isnt working properly on my spreadsheet | Excel Worksheet Functions |