求助各位大神！！！！！本人想把宏的代码做成一个小acticex插件，请各位帮忙优化一...

xzzj2019

Sub

    Range("C2").Select
    Selection.FormulaArray = "=IFERROR(VLOOKUP(RC[-1],R3C15:R24C16,2,0),"" "")"
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]-R[1]C[-2]=0,"" "",RC[-1])"
    Range("E2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C[-4],SMALL(IF(R2C4:R9000C4<>"" "",ROW(R2C4:R9000C4),"" ""),ROW(R[-1]C[-4]))),""  "")"
    Range("F2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C[-3],SMALL(IF(R2C4:R8000C4<>"" "",ROW(R2C4:R8000C4),"" ""),ROW(R[-1]C[-4]))),""  "")"
    Range("G2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C23,MATCH(RC[-2]&RC[-1],R2C12:R9000C12&R2C13:R9000C13,0)),RC[-2]&RC[-1])"
    Range("H2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C[-1],SMALL(IF(R2C7:R9000C7<>"" "",ROW(R2C7:R9000C7),"" ""),ROW(R[-1]C[-1]))),""  "")"
    Range("I2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C23,MATCH(RC[3]&RC[4],R2C5:R9000C5&R2C6:R9000C6,0)),RC[3]&RC[4])"
    Range("J2").Select
    Selection.FormulaArray = _
        "=IFERROR(INDEX(C[-1],SMALL(IF(R2C9:R9000C9<>"" "",ROW(R2C9:R9000C9),"" ""),ROW(R[-1]C[-1]))),""  "")"
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("C2:J2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FillDown
    Application.WindowState = xlMaximized
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    ActiveWindow.ScrollRow = 8975
    ActiveWindow.ScrollRow = 8954
    ActiveWindow.ScrollRow = 8933
    ActiveWindow.ScrollRow = 8913
    ActiveWindow.ScrollRow = 8892
    ActiveWindow.ScrollRow = 8871
    ActiveWindow.ScrollRow = 8850
    ActiveWindow.ScrollRow = 8808
    ActiveWindow.ScrollRow = 8788
    ActiveWindow.ScrollRow = 8725
    ActiveWindow.ScrollRow = 8684
    ActiveWindow.ScrollRow = 8579
    ActiveWindow.ScrollRow = 8496
    ActiveWindow.ScrollRow = 8392
    ActiveWindow.ScrollRow = 8288
    ActiveWindow.ScrollRow = 8163
    ActiveWindow.ScrollRow = 7996
    ActiveWindow.ScrollRow = 7809
    ActiveWindow.ScrollRow = 7684
    ActiveWindow.ScrollRow = 7518
    ActiveWindow.ScrollRow = 7372
    ActiveWindow.ScrollRow = 7184
    ActiveWindow.ScrollRow = 6976
    ActiveWindow.ScrollRow = 6789
    ActiveWindow.ScrollRow = 6643
    ActiveWindow.ScrollRow = 6435
    ActiveWindow.ScrollRow = 6310
    ActiveWindow.ScrollRow = 6164
    ActiveWindow.ScrollRow = 5956
    ActiveWindow.ScrollRow = 5789
    ActiveWindow.ScrollRow = 5623
    ActiveWindow.ScrollRow = 5415
    ActiveWindow.ScrollRow = 5310
    ActiveWindow.ScrollRow = 5165
    ActiveWindow.ScrollRow = 4998
    ActiveWindow.ScrollRow = 4894
    ActiveWindow.ScrollRow = 4811
    ActiveWindow.ScrollRow = 4707
    ActiveWindow.ScrollRow = 4623
    ActiveWindow.ScrollRow = 4540
    ActiveWindow.ScrollRow = 4394
    ActiveWindow.ScrollRow = 4332
    ActiveWindow.ScrollRow = 4269
    ActiveWindow.ScrollRow = 4186
    ActiveWindow.ScrollRow = 4124
    ActiveWindow.ScrollRow = 4061
    ActiveWindow.ScrollRow = 3957
    ActiveWindow.ScrollRow = 3853
    ActiveWindow.ScrollRow = 3811
    ActiveWindow.ScrollRow = 3749
    ActiveWindow.ScrollRow = 3686
    ActiveWindow.ScrollRow = 3645
    ActiveWindow.ScrollRow = 3603
    ActiveWindow.ScrollRow = 3520
    ActiveWindow.ScrollRow = 3499
    ActiveWindow.ScrollRow = 3457
    ActiveWindow.ScrollRow = 3416
    ActiveWindow.ScrollRow = 3374
    ActiveWindow.ScrollRow = 3332
    ActiveWindow.ScrollRow = 3270
    ActiveWindow.ScrollRow = 3228
    ActiveWindow.ScrollRow = 3187
    ActiveWindow.ScrollRow = 3124
    ActiveWindow.ScrollRow = 3083
    ActiveWindow.ScrollRow = 3020
    ActiveWindow.ScrollRow = 2916
    ActiveWindow.ScrollRow = 2854
    ActiveWindow.ScrollRow = 2812
    ActiveWindow.ScrollRow = 2770
    ActiveWindow.ScrollRow = 2729
    ActiveWindow.ScrollRow = 2666
    ActiveWindow.ScrollRow = 2604
    ActiveWindow.ScrollRow = 2541
    ActiveWindow.ScrollRow = 2479
    ActiveWindow.ScrollRow = 2416
    ActiveWindow.ScrollRow = 2354
    ActiveWindow.ScrollRow = 2312
    ActiveWindow.ScrollRow = 2229
    ActiveWindow.ScrollRow = 2187
    ActiveWindow.ScrollRow = 2146
    ActiveWindow.ScrollRow = 2104
    ActiveWindow.ScrollRow = 2062
    ActiveWindow.ScrollRow = 2041
    ActiveWindow.ScrollRow = 1979
    ActiveWindow.ScrollRow = 1937
    ActiveWindow.ScrollRow = 1896
    ActiveWindow.ScrollRow = 1854
    ActiveWindow.ScrollRow = 1812
    ActiveWindow.ScrollRow = 1771
    ActiveWindow.ScrollRow = 1729
    ActiveWindow.ScrollRow = 1667
    ActiveWindow.ScrollRow = 1646
    ActiveWindow.ScrollRow = 1625
    ActiveWindow.ScrollRow = 1604
    ActiveWindow.ScrollRow = 1583
    ActiveWindow.ScrollRow = 1542
    ActiveWindow.ScrollRow = 1521
    ActiveWindow.ScrollRow = 1479
    ActiveWindow.ScrollRow = 1458
    ActiveWindow.ScrollRow = 1438
    ActiveWindow.ScrollRow = 1396
    ActiveWindow.ScrollRow = 1375
    ActiveWindow.ScrollRow = 1354
    ActiveWindow.ScrollRow = 1313
    ActiveWindow.ScrollRow = 1292
    ActiveWindow.ScrollRow = 1271
    ActiveWindow.ScrollRow = 1229
    ActiveWindow.ScrollRow = 1209
    ActiveWindow.ScrollRow = 1188
    ActiveWindow.ScrollRow = 1146
    ActiveWindow.ScrollRow = 1105
    ActiveWindow.ScrollRow = 1042
    ActiveWindow.ScrollRow = 1021
    ActiveWindow.ScrollRow = 1000
    ActiveWindow.ScrollRow = 959
    ActiveWindow.ScrollRow = 917
    ActiveWindow.ScrollRow = 875
    ActiveWindow.ScrollRow = 855
    ActiveWindow.ScrollRow = 834
    ActiveWindow.ScrollRow = 813
    ActiveWindow.ScrollRow = 792
    ActiveWindow.ScrollRow = 771
    ActiveWindow.ScrollRow = 751
    ActiveWindow.ScrollRow = 730
    ActiveWindow.ScrollRow = 709
    ActiveWindow.ScrollRow = 688
    ActiveWindow.ScrollRow = 667
    ActiveWindow.ScrollRow = 646
    ActiveWindow.ScrollRow = 626
    ActiveWindow.ScrollRow = 605
    ActiveWindow.ScrollRow = 584
    ActiveWindow.ScrollRow = 563
    ActiveWindow.ScrollRow = 542
    ActiveWindow.ScrollRow = 522
    ActiveWindow.ScrollRow = 501
    ActiveWindow.ScrollRow = 480
    ActiveWindow.ScrollRow = 459
    ActiveWindow.ScrollRow = 438
    ActiveWindow.ScrollRow = 417
    ActiveWindow.ScrollRow = 397
    ActiveWindow.ScrollRow = 376
    ActiveWindow.ScrollRow = 355
    ActiveWindow.ScrollRow = 334
    ActiveWindow.ScrollRow = 313
    ActiveWindow.ScrollRow = 292
    ActiveWindow.ScrollRow = 272
    ActiveWindow.ScrollRow = 251
    ActiveWindow.ScrollRow = 230
    ActiveWindow.ScrollRow = 209
    ActiveWindow.ScrollRow = 188
    ActiveWindow.ScrollRow = 168
    ActiveWindow.ScrollRow = 147
    ActiveWindow.ScrollRow = 126
    ActiveWindow.ScrollRow = 105
    ActiveWindow.ScrollRow = 84
    ActiveWindow.ScrollRow = 63
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 22
    ActiveWindow.ScrollRow = 1
    Range("J5").Select
End Sub
Sub 宏2()
'
' 宏2 宏
'
' 快捷键: Ctrl+p
'
    Range("A2:J5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("L2:M3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
End Sub
这是我的宏代码，请各位大神帮帮忙感激不尽！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

xzzj2019

自己顶自己一下！！！！！！！！！！！！！！！！！！！！

约定的童话

你这录制的还是别做插件了加载宏倒是可以考虑

microyip

1. Sub 宏1()
   
2.     Dim nRow As Long
   
3.     nRow = Cells(Rows.Count, "C").End(xlUp).Row
   
4.     Range("C2:C" & nRow).FormulaArray = "=IFERROR(VLOOKUP(RC[-1],R3C15:R24C16,2,0),"" "")"
   
5.     nRow = Cells(Rows.Count, "D").End(xlUp).Row
   
6.     Range("D2:D" & nRow).FormulaR1C1 = "=IF(RC[-2]-R[1]C[-2]=0,"" "",RC[-1])"
   
7.     nRow = Cells(Rows.Count, "E").End(xlUp).Row
   
8.     Range("E2:E" & nRow).FormulaArray = _
   
9.         "=IFERROR(INDEX(C[-4],SMALL(IF(R2C4:R9000C4<>"" "",ROW(R2C4:R9000C4),"" ""),ROW(R[-1]C[-4]))),""  "")"
   
10.     nRow = Cells(Rows.Count, "F").End(xlUp).Row
    
11.     Range("F2:F" & nRow).FormulaArray = _
    
12.         "=IFERROR(INDEX(C[-3],SMALL(IF(R2C4:R8000C4<>"" "",ROW(R2C4:R8000C4),"" ""),ROW(R[-1]C[-4]))),""  "")"
    
13.     nRow = Cells(Rows.Count, "G").End(xlUp).Row
    
14.     Range("G2:G" & nRow).FormulaArray = _
    
15.         "=IFERROR(INDEX(C23,MATCH(RC[-2]&RC[-1],R2C12:R9000C12&R2C13:R9000C13,0)),RC[-2]&RC[-1])"
    
16.     nRow = Cells(Rows.Count, "H").End(xlUp).Row
    
17.     Range("H2:H" & nRow).FormulaArray = _
    
18.         "=IFERROR(INDEX(C[-1],SMALL(IF(R2C7:R9000C7<>"" "",ROW(R2C7:R9000C7),"" ""),ROW(R[-1]C[-1]))),""  "")"
    
19.     nRow = Cells(Rows.Count, "I").End(xlUp).Row
    
20.     Range("I2:I" & nRow).FormulaArray = _
    
21.         "=IFERROR(INDEX(C23,MATCH(RC[3]&RC[4],R2C5:R9000C5&R2C6:R9000C6,0)),RC[3]&RC[4])"
    
22.     nRow = Cells(Rows.Count, "J").End(xlUp).Row
    
23.     Range("J2:J" & nRow).FormulaArray = _
    
24.         "=IFERROR(INDEX(C[-1],SMALL(IF(R2C9:R9000C9<>"" "",ROW(R2C9:R9000C9),"" ""),ROW(R[-1]C[-1]))),""  "")"
    
25. End Sub
    
26. Sub 宏2()
    
27.     Dim nRow As Long
    
28.     nRow = Cells(Rows.Count, "A").End(xlUp).Row
    
29.     Range("A2:J" & nRow, "L2:M" & nRow).ClearContents
    
30. End Sub

复制代码

xzzj2019

microyip 发表于 2020-2-5 09:22

老师如果我想添加一个按钮这个代码要如何写啊 ，不想用宏因为到别的机器上总是会被禁止。

xzzj2019

microyip 发表于 2020-2-5 09:22

老师我只想提取H和J列的数据，运算之后并且把C-J列都清除掉，就相当于选择性粘贴H和J列其他列都清除要怎么做呢？请老师指教！！

xzzj2019

约定的童话 发表于 2020-2-5 08:46
你这录制的还是别做插件了加载宏倒是可以考虑

我想做一个公司所有人都可以用的，我录制宏到很多机器上就损坏了，而且我发现我录制宏之后运算过程时间太长.