Thirdonce.docx
《Thirdonce.docx》由会员分享，可在线阅读，更多相关《Thirdonce.docx（11页珍藏版）》请在冰豆网上搜索。
Thirdonce
DimcurSheetAsObject
DimCrow,Ccol,iAsLong
DimcompdateAsDate
DimtodayAsInteger
DimVendorAsString
DimUPCcol,VendorCol,VendornbrColAsInteger
SubThirdonceaweek（）
SetcurSheet=ActiveWorkbook.ActiveSheet
Application.DisplayAlerts=False
Application.ScreenUpdating=False
Columns（"U:
U"）.Select
Selection.Delete
Range（Cells（2,1）,Cells（80000,256））.SortKey1:
=Range（"D1"）
Range（Cells（2,1）,Cells（80000,256））.SortKey1:
=Range（"T1"）
r=[L1048576].End（xlUp）.Row
Range（"a"&r1+1）.Select
Selection.AutoFilterField:
=1,Criteria1:
=">=21"
Rows（"2:
2"）.Select
Range（Selection,Selection.End（xlDown））.Select
Selection.Delete
ActiveSheet.ShowAllData
Cells.Select
Selection.AutoFilterField:
=35,Criteria1:
="<>"
r=[C1048576].End（xlUp）.Row
Ifr<>1Then
Range（"D2:
D"&[C1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
Cells.Select
Selection.AutoFilterField:
=34,Criteria1:
="*ASN#*"
r=[C1048576].End（xlUp）.Row
Ifr<>1Then
Range（"D2:
D"&[C1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
'ESC
Range（"AH1"）.Select
Selection.AutoFilterField:
=34,Criteria1:
="=*ESC*",Operator:
=xlOr,_
Criteria2:
="=*CXL*"
r=[D1048576].End（xlUp）.Row
Ifr<>1Then
Range（"AH2:
AH"&[D1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
'e=2
'DoUntilCells（e,17）=Empty
'
'IfCells（e,13）="BABYCONSUMABLES"Then
'Rows（e）.Select
'
'WithSelection.Interior
'.Pattern=xlSolid
'.PatternColorIndex=xlAutomatic
'.ThemeColor=xlThemeColorAccent3
'.TintAndShade=0.399975585192419
'.PatternTintAndShade=0
'EndWith
'Else
'EndIf
'e=e+1
'Loop
Rows（"1,1"）.Select
Cells.Select
Selection.AutoFilterField:
=18,Criteria1:
="7"
Selection.AutoFilterField:
=9,Criteria1:
="C"
r=[D1048576].End（xlUp）.Row
Ifr<>1Then
Range（"Q2:
Q"&[D1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
Columns（"R:
R"）.Copy
Columns（"R:
R"）.InsertShift:
=xlToRight
Cells.Select
Selection.AutoFilterField:
=18,Criteria1:
="<>20"
Selection.AutoFilterField:
=19,Criteria1:
="<>3",Operator:
=xlAnd,Criteria2:
="<>7"
r=[C1048576].End（xlUp）.Row
Ifr<>1Then
Range（"D2:
D"&[C1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
Columns（"R:
R"）.Delete
today=Weekday（Date）
Iftoday=2Thencompdate=Date+4
Iftoday=3Thencompdate=Date+3
Iftoday=4Thencompdate=Date+2
Iftoday=5Thencompdate=Date+1
Iftoday=6Thencompdate=Date
Cells.Select
Selection.AutoFilterField:
=20,Criteria1:
=">"&compdate,Operator:
=xlAnd
r=[D1048576].End（xlUp）.Row
Ifr<>1Then
Range（"Q2:
Q"&[D1048576].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
Selection.EntireRow.Delete
EndIf
IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
UPCcol=Look_In_KeyWord（"UPC","c"）
VendorCol=Look_In_KeyWord（"VENDORNAME","c"）
'Crow=Fun_Rows_Columns_Count（"rows",UPCcol,100）
VendornbrCol=Look_In_KeyWord（"VENDORNUM","c"）
curSheet.Select
Sheets.Add
ActiveSheet.Name="DeleteVendor"
curSheet.Select
Range（Cells（1,1）,Cells（1,256））.Select
Selection.Copy
Sheets（"DeleteVendor"）.Select
Range（"A1"）.Select
ActiveSheet.Paste
curSheet.Activate
i=2
j=2
DoUntilcurSheet.Cells（i,VendornbrCol）.Value=Empty
Vendor=curSheet.Cells（i,VendorCol）.Value
IfInStr（UCase（Vendor）,"ELEXA"）>0OrInStr（UCase（Vendor）,"JVC"）>0OrInStr（UCase（Vendor）,"CANON"）>0_
OrInStr（UCase（Vendor）,"SONY"）>0Or（InStr（UCase（Vendor）,"PHILIPS"）>0And（（InStr（UCase（Vendor）,"PHILIPSDOMESTICAPPLIANCES"）=0）And（InStr（UCase（Vendor）,"PHILIPSDOMESTICAPPLIANCES"）=0）））OrInStr（UCase（Vendor）,"TTETECHNOLOGY"）>0_
OrInStr（UCase（Vendor）,"SAMSUNG"）>0OrInStr（UCase（Vendor）,"DELL"）>0OrInStr（UCase（Vendor）,"DISGUISE"）>0OrInStr（UCase（Vendor）,"MICROSOFT"）>0_
OrInStr（UCase（Vendor）,"MATTEL"）>0OrInStr（UCase（Vendor）,"HASBRO"）>0OrInStr（UCase（Vendor）,"MGA"）>0_
OrInStr（UCase（Vendor）,"RADICA"）>0OrInStr（UCase（Vendor）,"EASTMANKODAK"）>0OrInStr（UCase（Vendor）,"FISHERPRICEINC"）>0OrInStr（UCase（Vendor）,"FISHERPRICE"）>0OrInStr（UCase（Vendor）,"FISHER-PRICE"）>0_
OrInStr（UCase（Vendor）,"RADIOFLYER"）>0OrInStr（UCase（Vendor）,"PACIFICCYCLE"）>0OrInStr（UCase（Vendor）,"ACERAMERICA"）>0_
OrInStr（UCase（Vendor）,"P&FUSAINC"）>0OrInStr（UCase（Vendor）,"P&FUSAINC"）>0Then
IfcurSheet.Cells（i,VendornbrCol）.Value<>"364342050"Then
curSheet.Select
Range（Cells（i,1）,Cells（i,256））.Select
Selection.Copy
Sheets（"DeleteVendor"）.Select
Cells（j,1）.Select
ActiveSheet.Paste
curSheet.Select
Range（Cells（i,1）,Cells（i,256））.Select
Selection.DeleteShift:
=xlUp
j=j+1
i=i-1
EndIf
EndIf
i=i+1
Loop
Sheets（"DeleteVendor"）.Activate
'trackingnumber
'Range（"AA1"）.Select
'Selection.AutoFilterField:
=27,Criteria1:
="<>",Operator:
=xlAnd
'R=[D65536].End（xlUp）.Row
'IfR<>1Then
'Range（"D2:
D"&[D65536].End（xlUp）.Row）.SpecialCells（xlCellTypeVisible）.Select
'Selection.EntireRow.Delete
'EndIf
'IfActiveSheet.FilterMode=TrueThenActiveSheet.ShowAllData
'sort
'Cells.Select
'Selection.SortKey1:
=Range（"W2"）,Order1:
=xlAscending,Header:
=xlYes,_
'OrderCustom:
=1,MatchCase:
=False,Orientation:
=xlTopToBottom,SortMethod_
':
=xlPinYin,DataOption1:
=xlSortNormal
Columns（"A:
B"）.Select
Selection.InsertShift:
=xlToRight
Range（"A1"）.Select
ActiveCell.FormulaR1C1="Name"
Range（"B1"）.Select
ActiveCell.FormulaR1C1="Status"
Range（"A1:
bz"&[c65536].End（xlUp）.Row）.Copy
SettmpSheet=Workbooks.Add.Sheets
（1）
curSheet.Activate
Workbooks（"Book1"）.Activate
ActiveSheet.Paste
Columns（"M:
M"）.Select
Selection.Copy
Columns（"C:
C"）.Select
Selection.InsertShift:
=xlToRight
Columns（"AH:
AH"）.Select
Selection.Copy
Columns（"D:
D"）.Select
Selection.InsertShift:
=xlToRight
Columns（"U:
U"）.Select
Selection.Copy
Columns（"E:
E"）.Select
Selection.InsertShift:
=xlToRight
Columns（"AW:
AW"）.Select
Selection.Copy
Columns（"F:
F"）.Select
Selection.InsertShift:
=xlToRight
Columns（"AL:
AL"）.Select
Selection.Copy
Columns（"G:
G"）.Select
Selection.InsertShift:
=xlToRight
Columns（"H:
H"）.Select
Selection.InsertShift:
=xlToRight
Range（"H1"）="INVOICE"
Columns（"AC:
AC"）.Select
Selection.InsertShift:
=xlToRight
Range（"AC1"）="MABD"
Columns（"AF:
AF"）.Select
Selection.InsertShift:
=xlToRight
Range（"AF1"）="90%FILLED"
Columns（"E:
E"）.NumberFormatLocal="0000000000"
h=2
DoUntilCells（h,4）=Empty
IfCells（h,4）="75481065"Then
Cells（h,3）="3RD-ELECTRONICS"
ElseIfCells（h,4）="636654060"Then
Cells（h,3）="3RD-ELECTRONICS"
Else
EndIf
h=h+1
Loop
Range（Cells（2,1）,Cells（10000,100））.SortKey1:
=Range（"F1"）
Range（Cells（2,1）,Cells（10000,100））.SortKey1:
=Range（"E1"）
Range（Cells（2,1）,Cells（10000,100））.SortKey1:
=Range（"D1"）
Range（Cells（2,1）,Cells（10000,100））.SortKey1:
=Range（"C1"）
Range（"E2:
E"&[D65536].End（xlUp）.Row）.Copy
Sheets.Add.Name="adv"
Range（"a1"）.Select
ActiveSheet.Paste
Range（"A1:
A"&[a65536].End（xlUp）.Row）.Copy
Sheets.Add.Name="SQL"
Range（"a1"）.Select
ActiveSheet.Paste
Range（"B1:
B"&[a65536].End（xlUp）.Row）="=TEXT（RC[-1],""0000000000""）"
Range（"C1:
C"&[a65536].End（xlUp）.Row）="=""'""&RC[-1]&""',"""
Range（"A1:
A65536"）.AdvancedFilterAction:
=xlFilterInPlace,Unique:
=True
Sheets（"sheet3"）.Name="SQLresults"
Sheets（"adv"）.Delete
ActiveWorkbook.SaveAsFilename:
="C:
\Mail\once\"&Format（Date,"mmddyy"）&"_CHINA_OO_DETAILS--3rdemail.xls",FileFormat:
=xlNormal,Password:
="",WriteResPassword:
="",ReadOnlyRecommended:
=_
False,CreateBackup:
=False
Application.ScreenUpdating=True
MsgBox"MacroCompleted!
"
EndSub
升级会员 