VBA不用A列辅助编号改写逆顺 行数小于60先逆行到1 再从60 59逆下去

laoau138

VBA不用A列辅助编号改写逆顺   行数小于60先逆行到1   再从60 59逆下去

dsmch

1. Sub Macro1()
   
2. Dim arr, brr, d, i&, j%
   
3. Set d = CreateObject("scripting.dictionary")
   
4. arr = Range("a1").CurrentRegion
   
5. brr = [p1:t60]
   
6. For i = 1 To UBound(arr)
   
7.     d(arr(i, 2)) = i
   
8. Next
   
9. s = UBound(brr)
   
10. n = d(brr(s, 1))
    
11. For j = 2 To UBound(arr, 2)
    
12.     brr(s, j - 1) = arr(n, j)
    
13. Next
    
14. For i = s - 1 To 1 Step -1
    
15.     n = n - 1
    
16.     If n = 0 Then n = 60
    
17.     For j = 2 To UBound(arr, 2)
    
18.         brr(i, j - 1) = arr(n, j)
    
19.     Next
    
20. Next
    
21. [p1:t60] = brr
    
22. End Sub

复制代码

sandorn

1. Sub 无辅助列方法()
   
2.     Application.ScreenUpdating = False
   
3.     k = Application.Match(Range("p60").Value, Range("b1:b60"), 0)
   
4.     If IsError(k) Then MsgBox "P60单元格填写错误": Exit Sub
   
5.    
   
6.     j = 60
   
7.     For i = k To 1 Step -1
   
8.         Range("B" & i & ":F" & i).Copy Cells(j, 16)
   
9.         j = j - 1
   
10.     Next i
    
11. 
    
12.     If k <> 60 Then
    
13.         For m = 60 To k + 1 Step -1
    
14.             Range("B" & m & ":F" & m).Copy Cells(j, 16)
    
15.             j = j - 1
    
16.         Next m
    
17.     End If
    
18.    
    
19.     Application.ScreenUpdating = True
    
20. End Sub

复制代码

附件发不上来，网络问题。代码足够了

laoau138

dsmch 发表于 2017-1-12 20:03

这个速度更快一点

laoau138

dsmch 发表于 2017-1-12 20:03

VBA看图片规则计算A列结果

http://www.excelpx.com/thread-427160-1-1.html

laoau138

sandorn 发表于 2017-1-12 20:25
附件发不上来，网络问题。代码足够了

VBA看图片规则计算A列结果

http://www.excelpx.com/thread-427160-1-1.html