B
BeSmart
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
....
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
....