[發問] IF函數，代碼簡化問題

fusayloveme

請問各位大大，以下這些代碼是否能簡化呢!? 一直找不到感覺用不同的方法寫，讓我感覺有點挫折阿 ><，先謝過大大們....:)

1. Private Sub CommandButton2_Click()
   
2. 
   
3.     Range("S1").Select
   
4.     ActiveCell.FormulaR1C1 = "編號"
   
5.     Range("S2").Select
   
6.     ActiveCell.FormulaR1C1 = "=IF(R[1]C[-17]=R[0]C[-17],1,0)"
   
7.     Selection.AutoFill Destination:=Range("S2:S600"), Type:=xlFillDefault
   
8.     Range("S2:S600").Select
   
9.     Range("T1").Select
   
10.     ActiveCell.FormulaR1C1 = "製程"
    
11.     Range("T2").Select
    
12.     ActiveCell.FormulaR1C1 = "=IF(R[1]C[-16]=R[0]C[-16],1,0)"
    
13.     Selection.AutoFill Destination:=Range("T2:T600"), Type:=xlFillDefault
    
14.     Range("T2:T600").Select
    
15.     Range("U1").Select
    
16.     ActiveCell.FormulaR1C1 = "物料名稱"
    
17.     Range("U2").Select
    
18.     ActiveCell.FormulaR1C1 = "=IF(R[1]C[-9]=R[0]C[-9],1,0)"
    
19.     Selection.AutoFill Destination:=Range("U2:U600"), Type:=xlFillDefault
    
20.     Range("U2:U600").Select
    
21.     Range("V1").Select
    
22.     ActiveCell.FormulaR1C1 = "代碼"
    
23.     Range("V2").Select
    
24.     ActiveCell.FormulaR1C1 = "=IF(R[1]C[-13]=R[0]C[-13],1,0)"
    
25.     Selection.AutoFill Destination:=Range("V2:V600"), Type:=xlFillDefault
    
26.     Range("V2:V600").Select
    
27.     Range("W1").Select
    
28.     ActiveCell.FormulaR1C1 = "總加"
    
29.     Range("W2").Select
    
30.     ActiveCell.FormulaR1C1 = "=SUM(RC[-4]:RC[-1])"
    
31.     Selection.AutoFill Destination:=Range("W2:W600"), Type:=xlFillDefault
    
32.     Range("W1").Select
    
33.     Selection.AutoFilter
    
34.     ActiveSheet.Range("$S$1:$W$600").AutoFilter Field:=5, Criteria1:="4"
    
35.     Columns("E:H").Select
    
36.     Selection.EntireColumn.Hidden = True
    
37. End Sub

複製代碼

GBKEE

回復 1# fusayloveme

1. Option Explicit
   
2. Private Sub CommandButton2_Click()
   
3.     Range("S1").Resize(, 5) = Array("編號", "製程", "物料名稱", "代碼", "總加")
   
4.     'Range("S1:W1") = Array("編號", "製程", "物料名稱", "代碼", "總加")   '加上註解 系統不會執行
   
5.     With Range("S2:W600")
   
6.         .Columns(1) = "=IF(R[1]C[-17]=R[0]C[-17],1,0)"
   
7.         .Columns(2) = "=IF(R[1]C[-16]=R[0]C[-16],1,0)"
   
8.         .Columns(3) = "=IF(R[1]C[-9]=R[0]C[-9],1,0)"
   
9.         .Columns(4) = "=IF(R[1]C[-13]=R[0]C[-13],1,0)"
   
10.         .Columns(5) = "=SUM(RC[-4]:RC[-1])"
    
11.     End With
    
12.     'Range("S2:S600") = "=IF(R[1]C[-17]=R[0]C[-17],1,0)"
    
13.     'Range("T2:T600") = "=IF(R[1]C[-16]=R[0]C[-16],1,0)"
    
14.     'Range("U2:U600") = "=IF(R[1]C[-9]=R[0]C[-9],1,0)"
    
15.     'Range("V2:V600") = "=IF(R[1]C[-13]=R[0]C[-13],1,0)"
    
16.     'Range("W2:W600") = "=SUM(RC[-4]:RC[-1])"
    
17.     Range("W1").AutoFilter
    
18.     ActiveSheet.Range("$S$1:$W$600").AutoFilter Field:=5, Criteria1:="4"
    
19.     Columns("E:H").EntireColumn.Hidden = True
    
20. End Sub

複製代碼

fusayloveme

回復 2# GBKEE
感謝GBKEE大!!!，又讓我學到不少! 超感恩^^