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

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/

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

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/