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/
|