Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2k VBA problem - For loops interating a sort
Running MS Office 2000, SP3 on NT 4 SP6
Goal: Given a workbook in which worksheet 2, "Data" has the form: H T U Account Name Jan 04 Feb 04 account name1 #s for jan #s for feb account name2 ... account name1 account name4 ... this does the full year so it goes to AE. I'm trying to have a macro calculate the top 10 and top 20 for 'accoun name1' per month and store them in worksheet 3, "Derived" in whic account name1's top 10 for column T go into B8 and the top 20 into b9 then the top 10/20 for column U go into c8,c9 &c. I can sort by account name1 and one of the columns but it would hurt m to use copy and paste 12 times instead of a for loop! I'm brand new t VBA (started yesterday, got this far from reading previous posts o this forum) and really appreciate your help! Thanks in advance, Rob Code: Sub TopTenTwenty() i = 2 For j = 20 To 31 ActiveWorkbook.Sheets("Data").Activate Sheets("Data").Range("A:AE").Sort _ Key1:=Worksheets("Data").Columns("H"), _ Order1:=xlAscending, _ Key2:=Worksheets("Data").Columns(j), _ Order2:=x1Descending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom 'Takes top ten/twenty ActiveWorkbook.Sheets("Derived").Activate ActiveSheet.Cells(8, i).Value WorksheetFunction.Sum(Worksheets("Data").Range(Cel ls(2, j), Cells(11 j))) ActiveSheet.Cells(9, i).Value WorksheetFunction.Sum(Worksheets("Data").Range(Cel ls(2, j), Cells(21 j))) i = i + 1 Next j End Su -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2k VBA problem - For loops interating a sort
Look up the LARGE function in XL help.
robert_m_mills < wrote: Running MS Office 2000, SP3 on NT 4 SP6 Goal: Given a workbook in which worksheet 2, "Data" has the form: H T U Account Name Jan 04 Feb 04 account name1 #s for jan #s for feb account name2 ... account name1 account name4 .. this does the full year so it goes to AE. I'm trying to have a macro calculate the top 10 and top 20 for 'account name1' per month and store them in worksheet 3, "Derived" in which account name1's top 10 for column T go into B8 and the top 20 into b9, then the top 10/20 for column U go into c8,c9 &c. I can sort by account name1 and one of the columns but it would hurt me to use copy and paste 12 times instead of a for loop! I'm brand new to VBA (started yesterday, got this far from reading previous posts on this forum) and really appreciate your help! Thanks in advance, Rob Code: Sub TopTenTwenty() i = 2 For j = 20 To 31 ActiveWorkbook.Sheets("Data").Activate Sheets("Data").Range("A:AE").Sort _ Key1:=Worksheets("Data").Columns("H"), _ Order1:=xlAscending, _ Key2:=Worksheets("Data").Columns(j), _ Order2:=x1Descending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom 'Takes top ten/twenty ActiveWorkbook.Sheets("Derived").Activate ActiveSheet.Cells(8, i).Value = WorksheetFunction.Sum(Worksheets("Data").Range(Cel ls(2, j), Cells(11, j))) ActiveSheet.Cells(9, i).Value = WorksheetFunction.Sum(Worksheets("Data").Range(Cel ls(2, j), Cells(21, j))) i = i + 1 Next j End Sub --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2k VBA problem - For loops interating a sort
wouldn't that mean reading each column into the array and then callin
large 20 times while adding the value to an accumulator and writing i @ 10 values and 20 values -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2k VBA problem - For loops interating a sort
yeah it would. It looks like it would be better to use Pivot Tables
instead of VBA. robert_m_mills < wrote: wouldn't that mean reading each column into the array and then calling large 20 times while adding the value to an accumulator and writing it @ 10 values and 20 values? --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel 2007 sort problem | Excel Worksheet Functions | |||
excel sort problem | Excel Worksheet Functions | |||
if function in excel should 12 loops | Excel Discussion (Misc queries) | |||
Excel 2000 Slow Loops | Excel Programming | |||
excel 97: if then and for next loops help | Excel Programming |