|
楼主 |
发表于 2008-8-29 15:33
|
显示全部楼层
<p>偶的代码</p><p>rivate Sub Worksheet_ActivAte()<br/>Dim x%, y%, z%, xx%, yy%, zz%, arr, arra, arrb(), arrc(1 To 10, 1 To 5), sh%, k%, k1%, mm%, nn%, nnn%<br/>Dim xa%, xb%, xn%, xz%, arrd(), arre, cisu1%, cisu2%, paixu1%, paixu2%, paixucl%, paixuhou1%, paixuhou2%<br/>Dim xfjsarr, arrdd(), arrddd(), nnnn%, abcd%, dcba%, abc%<br/>arr = Array("A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10") '队名<br/>k = k + 1<br/>abc = 0<br/>ReDim Preserve arrb(1 To 10, 1 To k)<br/>For x = 0 To 9<br/> arrb(x + 1, 1) = arr(x)<br/>Next<br/>For sh = 1 To Sheets.Count<br/> If Sheets(sh).Name <> "汇总" Then<br/> k = k + 5<br/> k1 = k1 + 1<br/> arra = Sheets(sh).Range("a3:f7")<br/> End If<br/> If Sheets(sh).Name <> "汇总" Then<br/> For abcd = 1 To 4<br/> For dcba = 1 To 5<br/> If arra(dcba, dcba) = "" Then abc = abc + 1<br/> Next dcba<br/> Next abcd<br/> End If<br/> If abc > 0 Then MsgBox "某个表没有输入完整": End<br/> ReDim Preserve arrb(1 To 10, 1 To k)<br/> For y = 1 To 10<br/> For z = 1 To 5<br/> If arra(z, 1) = arrb(y, 1) Then<br/> arrb(y, k - 4) = arra(z, 2)<br/> arrb(y, k - 3) = arra(z, 3)<br/> arrb(y, k - 2) = arra(z, 5)<br/> arrb(y, k - 1) = arra(z, 4)<br/> arrb(y, k) = arra(z, 6)<br/> End If<br/> Next z<br/> For z = 1 To 5<br/> If arra(z, 2) = arrb(y, 1) Then<br/> arrb(y, k - 4) = arra(z, 1)<br/> arrb(y, k - 3) = arra(z, 4)<br/> arrb(y, k - 2) = arra(z, 6)<br/> arrb(y, k - 1) = arra(z, 3)<br/> arrb(y, k) = arra(z, 5)<br/> End If<br/> Next z<br/> Next y<br/>Next sh<br/>For yy = 1 To 10<br/> For zz = 1 To k1<br/> arrc(yy, 1) = arrc(yy, 1) + arrb(yy, 4 + (zz - 1) * 5)<br/> If arrb(yy, 4 + (zz - 1) * 5) > 15 Then arrc(yy, 2) = arrc(yy, 2) + 1<br/> If arrc(yy, 2) = "" Then arrc(yy, 2) = 0<br/> mm = mm + arrb(yy, 6 + (zz - 1) * 5)<br/> nn = nn + arrb(yy, 3 + (zz - 1) * 5)<br/> nnn = nnn + arrb(yy, 5 + (zz - 1) * 5)<br/> Next zz<br/> arrc(yy, 3) = mm / k1<br/> arrc(yy, 4) = nn / nnn<br/> mm = 0: nn = 0: nnn = 0<br/>Next yy<br/>For xa = 1 To 10<br/> For xb = 1 To 10<br/> If arrc(xb, 1) > arrc(xa, 1) Then<br/> xn = xn + 1<br/> End If<br/> Next xb<br/> arrc(xa, 5) = xn + 1<br/> xn = 0<br/>Next xa</p><p>arre = arrc<br/>cisu1 = 0</p><p>For xa = 1 To 10<br/> ReDim arrd(1 To 10, 1 To 1)<br/> For xb = 1 To 10<br/> If xa <> xb Then<br/> If arrc(xa, 5) <> "" Then<br/> If arrc(xb, 5) = arrc(xa, 5) Then<br/> cisu1 = cisu1 + 1<br/> arrd(xa, 1) = arrb(xa, 4)<br/> For xz = 2 To UBound(arrb, 2)<br/> If arrb(xa, xz) = arrb(xb, 1) Then<br/> arrd(xb, 1) = arrb(xa, xz + 4)<br/> cisu2 = cisu2 + 1<br/> End If<br/> Next xz<br/> arrc(xb, 5) = ""<br/> End If<br/> End If<br/> End If<br/> Next xb<br/> <br/> If cisu1 <> 0 Then<br/> If cisu1 = cisu2 Then<br/> For paixu1 = 1 To 9<br/> For paixu2 = 1 To 10 - paixu1<br/> If arrd(paixu2, 1) <> "" Then<br/> If arrd(paixu2, 1) < arrd(paixu2 + 1, 1) Then<br/> paixucl = arrd(paixu2, 1): arrd(paixu2, 1) = arrd(paixu2 + 1, 1): arrd(paixu2 + 1, 1) = paixucl<br/> End If<br/> End If<br/> Next paixu2<br/> Next paixu1<br/> For paixuhou1 = 1 To 10<br/> If arrd(paixuhou1, 1) <> "" Then<br/> paixuhou2 = paixuhou2 + 1<br/> arre(paixuhou1, 5) = arre(paixuhou1, 5) + paixuhou2 - 1<br/> End If<br/> Next paixuhou1<br/> paixuhou2 = 0<br/> End If<br/> End If</p><p>arrc(xa, 5) = ""<br/>cisu1 = 0<br/>cisu2 = 0<br/>Next xa<br/>''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''<br/>cisu1 = 0: cisu2 = 0<br/>xfjsarr = arre<br/>For xa = 1 To 10<br/> If xfjsarr(xa, 5) <> "" Then<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xa, 2)<br/> ReDim arrd(1 To 10, 1 To 1)<br/> arrd(xa, 1) = xfjsarr(xa, 2)<br/> For xb = 1 To 10<br/> If xa <> xb Then<br/> If xfjsarr(xb, 5) = xfjsarr(xa, 5) Then<br/> arrd(xb, 1) = xfjsarr(xb, 2)<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xb, 2)<br/> xfjsarr(xb, 5) = ""<br/> nnnn = nnnn + 1<br/> End If<br/> End If<br/> Next xb<br/> cisu1 = 0<br/> End If<br/> <br/> If nnnn > 0 Then xfjsarr(xa, 5) = ""<br/> nnnn = 0</p><p> If UBound(arrdd) > 1 Then ''去重复<br/> For paixu1 = 1 To UBound(arrdd)<br/> If arrdd(paixu1) <> "" Then<br/> For paixu2 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) = arrdd(paixu2) Then<br/> arrdd(paixu2) = ""<br/> End If<br/> End If<br/> Next paixu2<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If UBound(arrdd) > 1 Then ''得到不重复的数组<br/> For paixu1 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) <> "" Then<br/> cisu2 = cisu2 + 1<br/> ReDim Preserve arrddd(1 To cisu2)<br/> arrddd(cisu2) = arrdd(paixu1)<br/> End If<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If cisu2 > 0 Then<br/> ''排序<br/> For paixu1 = 1 To UBound(arrddd) - 1<br/> For paixu2 = 1 To UBound(arrddd) - paixu1<br/> If arrddd(paixu2) < arrddd(paixu2 + 1) Then<br/> paixucl = arrddd(paixu2): arrddd(paixu2) = arrddd(paixu2 + 1): arrddd(paixu2 + 1) = paixucl<br/> cisu2 = cisu2 + 1<br/> End If<br/> Next paixu2<br/> Next paixu1<br/> <br/> For paixuhou1 = 1 To UBound(arrddd)<br/> For paixuhou2 = 1 To 10<br/> If xfjsarr(paixuhou2, 5) = "" Then<br/> If Val(arre(paixuhou2, 2)) = Val(arrddd(paixuhou1)) Then<br/> arre(paixuhou2, 5) = arre(paixuhou2, 5) + paixuhou1 - 1<br/> End If<br/> End If<br/> Next paixuhou2<br/> Next paixuhou1<br/> End If<br/>cisu2 = 0<br/>ReDim arrddd(1 To 1)<br/>xfjsarr = arre<br/>Next xa<br/>'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''<br/>cisu1 = 0: cisu2 = 0<br/>xfjsarr = arre<br/>For xa = 1 To 10<br/> If xfjsarr(xa, 5) <> "" Then<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xa, 3)<br/> ReDim arrd(1 To 10, 1 To 1)<br/> arrd(xa, 1) = xfjsarr(xa, 3)<br/> For xb = 1 To 10<br/> If xa <> xb Then<br/> If xfjsarr(xb, 5) = xfjsarr(xa, 5) Then<br/> arrd(xb, 1) = xfjsarr(xb, 3)<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xb, 3)<br/> xfjsarr(xb, 5) = ""<br/> nnnn = nnnn + 1<br/> End If<br/> End If<br/> Next xb<br/> cisu1 = 0<br/> End If<br/> <br/> If nnnn > 0 Then xfjsarr(xa, 5) = ""<br/> nnnn = 0</p><p> If UBound(arrdd) > 1 Then ''去重复<br/> For paixu1 = 1 To UBound(arrdd)<br/> If arrdd(paixu1) <> "" Then<br/> For paixu2 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) = arrdd(paixu2) Then<br/> arrdd(paixu2) = ""<br/> End If<br/> End If<br/> Next paixu2<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If UBound(arrdd) > 1 Then ''得到不重复的数组<br/> For paixu1 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) <> "" Then<br/> cisu2 = cisu2 + 1<br/> ReDim Preserve arrddd(1 To cisu2)<br/> arrddd(cisu2) = arrdd(paixu1)<br/> End If<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If cisu2 > 0 Then<br/> ''排序<br/> For paixu1 = 1 To UBound(arrddd) - 1<br/> For paixu2 = 1 To UBound(arrddd) - paixu1<br/> If arrddd(paixu2) < arrddd(paixu2 + 1) Then<br/> paixucl = arrddd(paixu2): arrddd(paixu2) = arrddd(paixu2 + 1): arrddd(paixu2 + 1) = paixucl<br/> cisu2 = cisu2 + 1<br/> End If<br/> Next paixu2<br/> Next paixu1<br/> <br/> For paixuhou1 = 1 To UBound(arrddd)<br/> For paixuhou2 = 1 To 10<br/> If xfjsarr(paixuhou2, 5) = "" Then<br/> If Val(arre(paixuhou2, 3)) = Val(arrddd(paixuhou1)) Then<br/> arre(paixuhou2, 5) = arre(paixuhou2, 5) + paixuhou1 - 1<br/> End If<br/> End If<br/> Next paixuhou2<br/> Next paixuhou1<br/> End If<br/>cisu2 = 0<br/>ReDim arrddd(1 To 1)<br/>xfjsarr = arre<br/>Next xa<br/> <br/>'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''<br/>cisu1 = 0: cisu2 = 0<br/>xfjsarr = arre<br/>For xa = 1 To 10<br/> If xfjsarr(xa, 5) <> "" Then<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xa, 4)<br/> ReDim arrd(1 To 10, 1 To 1)<br/> arrd(xa, 1) = xfjsarr(xa, 4)<br/> For xb = 1 To 10<br/> If xa <> xb Then<br/> If xfjsarr(xb, 5) = xfjsarr(xa, 5) Then<br/> arrd(xb, 1) = xfjsarr(xb, 4)<br/> cisu1 = cisu1 + 1<br/> ReDim Preserve arrdd(1 To cisu1)<br/> arrdd(cisu1) = xfjsarr(xb, 4)<br/> xfjsarr(xb, 5) = ""<br/> nnnn = nnnn + 1<br/> End If<br/> End If<br/> Next xb<br/> cisu1 = 0<br/> End If<br/> <br/> If nnnn > 0 Then xfjsarr(xa, 5) = ""<br/> nnnn = 0</p><p> If UBound(arrdd) > 1 Then ''去重复<br/> For paixu1 = 1 To UBound(arrdd)<br/> If arrdd(paixu1) <> "" Then<br/> For paixu2 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) = arrdd(paixu2) Then<br/> arrdd(paixu2) = ""<br/> End If<br/> End If<br/> Next paixu2<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If UBound(arrdd) > 1 Then ''得到不重复的数组<br/> For paixu1 = 1 To UBound(arrdd)<br/> If paixu1 <> paixu2 Then<br/> If arrdd(paixu1) <> "" Then<br/> cisu2 = cisu2 + 1<br/> ReDim Preserve arrddd(1 To cisu2)<br/> arrddd(cisu2) = arrdd(paixu1)<br/> End If<br/> End If<br/> Next paixu1<br/> End If<br/> <br/> If cisu2 > 0 Then<br/> ''排序<br/> For paixu1 = 1 To UBound(arrddd) - 1<br/> For paixu2 = 1 To UBound(arrddd) - paixu1<br/> If arrddd(paixu2) < arrddd(paixu2 + 1) Then<br/> paixucl = arrddd(paixu2): arrddd(paixu2) = arrddd(paixu2 + 1): arrddd(paixu2 + 1) = paixucl<br/> cisu2 = cisu2 + 1<br/> End If<br/> Next paixu2<br/> Next paixu1<br/> <br/> For paixuhou1 = 1 To UBound(arrddd)<br/> For paixuhou2 = 1 To 10<br/> If xfjsarr(paixuhou2, 5) = "" Then<br/> If Val(arre(paixuhou2, 4)) = Val(arrddd(paixuhou1)) Then<br/> arre(paixuhou2, 5) = arre(paixuhou2, 5) + paixuhou1 - 1<br/> End If<br/> End If<br/> Next paixuhou2<br/> Next paixuhou1<br/> End If<br/>cisu2 = 0<br/>ReDim arrddd(1 To 1)<br/>xfjsarr = arre<br/>Next xa<br/> <br/>'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''<br/>Sheets("汇总").Range("a3").Resize(10, UBound(arrb, 2)) = arrb<br/>Sheets("汇总").Cells(3, UBound(arrb, 2) + 1).Resize(10, UBound(arre, 2)) = arre<br/>Sheets("汇总").Cells(2, UBound(arrb, 2) + UBound(arre, 2)) = "名次"<br/>End Sub</p> |
|