View Full Version : speed macro using array
lavenderial
07-20-2008, 06:25 PM
hye all,
I have to do a project which required me to get specific data from a large excel doc. i use filtering function but it takes me about 20 minutes to come out with the result. I know we can do it using array, but i don't know how to do it. for example, i want a data from country : xxxx, account name : yyyy unit:zzzz revenue :1234, these data i have to paste it to another worksheet, now i'm using macro and it is very slow. what i'm not rite now is juz record a macro, that's why it is slow. can sumbody help me to speed up my process?i'm am more than happy to receive your help.
Bob Phillips
07-21-2008, 12:47 AM
Maybe if we see the code we can. Record does create inefficient code.
You should look at analysing the data with pivot tables.
lavenderial
07-21-2008, 12:51 AM
This is one example of the code. Can you help me to speed up the macro?
Sub php_1()
'GSM
Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter
Selection.AutoFilter
Selection.AutoFilter Field:=16, Criteria1:="2008"
Selection.AutoFilter Field:=17, Criteria1:="1"
Selection.AutoFilter Field:=22, Criteria1:="<>tobeclosed", Operator:= _
xlAnd
Selection.AutoFilter Field:=2, Criteria1:="Philippines"
Selection.AutoFilter Field:=9, Criteria1:="GSMWD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'WIMAX
Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="WIMAX"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'WCDMA
Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="WCDMAD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("E4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'CDMA
Windows("Order PSM.xls").Activate
Sheets("Original").Select
Rows("1:1").Select
Selection.AutoFilter Field:=9, Criteria1:="CDMAD"
Columns("R:R").Select
Selection.Copy
Windows("Countries.xls").Activate
Sheets("php").Select
Range("F4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
--pivot table?can u tell me how can pivot table can help me speed up the macro. tanx a lot for ur reply:friends:
Bob Phillips
07-21-2008, 01:03 AM
This is untested as I have nothing to test it on, but see if this is quicker
Sub php_1()
Application.ScreenUpdating = False
With Workbooks("Order PSM.xls")
With Sheets("Original")
With .Rows("1:1")
.AutoFilter Field:=16, Criteria1:="2008"
.AutoFilter Field:=17, Criteria1:="1"
.AutoFilter Field:=22, Criteria1:="<>tobeclosed"
.AutoFilter Field:=2, Criteria1:="Philippines"
End With
'GSM
.Rows("1:1").AutoFilter Field:=9, Criteria1:="GSMWD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("A4").PasteSpecial Paste:=xlValues
'WIMAX
.Rows("1:1").AutoFilter Field:=9, Criteria1:="WIMAX"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("D4").PasteSpecial Paste:=xlValues
'WCDMA
.Rows("1:1").AutoFilter Field:=9, Criteria1:="WCDMAD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("E4").PasteSpecial Paste:=xlValues
'CDMA
.Rows("1:1").AutoFilter Field:=9, Criteria1:="CDMAD"
.Columns("R:R").Copy
Workbooks("Countries.xls").Worksheets("php").Range("F4").PasteSpecial Paste:=xlValues
End With
End With
Application.ScreenUpdating = True
End Sub
lavenderial
07-21-2008, 01:30 AM
what if I want to do it using array? for example array that will matches the country and account together with the revenue. for example, in a large worksheet, there are a lot of data in it. from this large worksheet i want the array to find specific info,for instance sri lanka as the country, tigo as the account and get the revenue and paste it in the other worksheet.then, i want the array to find the same country but different account n etc. I know i can do it using array, but I don't know the way. can u help me? tanx a lot!:thumb:thumb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.