資源描述:
《excel自動排序vba代碼》由會員上傳分享,免費(fèi)在線閱讀,更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫。
1、Sub單列自動排序()'a為需要排序的數(shù)組,up為True則為升序排列,為False,則為降序排列。DimupAsBooleanDimranktypeAsStringDimiAsInteger,jAsIntegerDimtempAsDouble,temp1AsIntegerDima(1000,3)AsDouble'a數(shù)組定義大小要超過排序數(shù),此處是按排序數(shù)在1000個以內(nèi)定義'*********************colnum=1'排序數(shù)據(jù)列rankcolnum=2'排序碼列startrow=5'排序數(shù)據(jù)起始行號
2、endrow=135'排序數(shù)據(jù)末尾行號ranktype="降序"'排序方式,“升序”或“降序”Ifranktype="升序"Thenup=TrueElseup=FalseEndIfFori=1Toendrow-startrow+1a(i,0)=Val(ActiveSheet.Cells(startrow+i-1,colnum))a(i,1)=startrow+i-1a(i,2)=0Next'*********************Fori=1Toendrow-startrow'進(jìn)行n-1輪比較Forj=endrow
3、-startrow+1Toi+1Step-1'從n到i個元素兩兩進(jìn)行比較IfupThen'判斷升降序Ifa(j,0)a(j-1,0)Thentemp=a(j,0)temp1=a(j,1)a(j,0)=a(j-1,0)a(j,1)=a(j-1,1)a(j-1,0)=tempa(j-1,1)=temp1
4、EndIfEndIfNextjNextiFori=1Toendrow-startrow+1a(i,2)=iNextFori=2Toendrow-startrow+1Ifa(i,0)=a(i-1,0)Thena(i,2)=a(i-1,2)EndIfNextFori=1Toendrow-startrow+1ActiveSheet.Cells(a(i,1),rankcolnum)=a(i,2)NextEndSub'數(shù)據(jù)自動排序(單列間隔)Sub自動排序1()'a為需要排序的數(shù)組,up為True則為升序排列,為False,則
5、為降序排列。DimupAsBooleanup=FalseDimiAsInteger,jAsIntegerDimtempAsDouble,temp1AsIntegerForcolnum=1To9'9為需要排序的數(shù)據(jù)列數(shù)Dima(100,3)AsDouble'*********************startrow=3'排序數(shù)據(jù)起始行號endrow=20'排序數(shù)據(jù)末尾行號Fori=1Toendrow-startrow+1a(i,0)=Val(ActiveSheet.Cells(startrow+i-1,(colnum-
6、1)*2+4))a(i,1)=startrow+i-1a(i,2)=0Next'*********************Fori=1Toendrow-startrow'進(jìn)行n-1輪比較Forj=endrow-startrow+1Toi+1Step-1'從n到i個元素兩兩進(jìn)行比較IfupThen'判斷升降序Ifa(j,0)7、lseIfa(j,0)>a(j-1,0)Thentemp=a(j,0)temp1=a(j,1)a(j,0)=a(j-1,0)a(j,1)=a(j-1,1)a(j-1,0)=tempa(j-1,1)=temp1EndIfEndIfNextjNextiFori=1Toendrow-startrow+1a(i,2)=iNextFori=2Toendrow-startrow+1Ifa(i,0)=a(i-1,0)Thena(i,2)=a(i-1,2)EndIfNextFori=1Toendrow-startrow+1Activ
8、eSheet.Cells(a(i,1),(colnum-1)*2+5)=a(i,2)NextNextEndSub