Recordset - Urgent help need

[Tim]

Hi folks,

I have the following data (partial data - Total over 2000 records.) in excel
spreadsheet - sheet1. I would like to select top 10 of sales and multiply
the commission by 1.05 in ado recordset and update the commision column.
Please help.

Thanks in advance.
Tim.

Name Sales Commission
Pete 1000 40
John 500 20
Dave 1900 76
Mary 566 22.64

 

[ryguy7272]

Maybe something like this...

Sub Macro1()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:C11").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Range("H2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*1.05"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H11")
Range("H2:H11").Select
End Sub

Regards,
Ryan---

PS, a Pivot Table will give you the top 10 as well...

 

[Tim]

Hi ryguy7272,

Thanks for your code but I can't use it because I can't change the order.
It links to orher spreadsheet and I can't add any sheet neither because it is
protected. Therefore, I think ado recordset will be my only choice. Could
you show me how to use ado recordset?

Thanks in advance.
Tim.

Maybe something like this...

Sub Macro1()

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1:C11").Select
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Range("H2").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-1]*1.05"
Range("H2").Select
Selection.AutoFill Destination:=Range("H2:H11")
Range("H2:H11").Select
End Sub

Regards,
Ryan---

PS, a Pivot Table will give you the top 10 as well...

--
RyGuy

Hi folks,

I have the following data (partial data - Total over 2000 records.) in excel
spreadsheet - sheet1. I would like to select top 10 of sales and multiply
the commission by 1.05 in ado recordset and update the commision column.
Please help.

Thanks in advance.
Tim.

Name Sales Commission
Pete 1000 40
John 500 20
Dave 1900 76
Mary 566 22.64