標題: [發問] worksheet_change 填滿問題 [打印本頁]

---

作者: s13030029    時間: 2019-5-31 11:33     標題: worksheet_change 填滿問題

我程式是在E欄到F欄的儲存格中輸入"0"顯示OK，輸入"1"為NG，但是如果我想要向下或向右拉填滿就會出錯，有人知道是哪裡出問題了嗎?

1. Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   
2. 
   
3.     If Target.Column = 5 Then
   
4.         ThisRow = Target.Row
   
5.         If Target.Value = "0" Then
   
6.             Range("E" & ThisRow).Value = "OK"
   
7.         ElseIf Target.Value = "1" Then
   
8.             Range("E" & ThisRow).Value = "NG"
   
9.         Else
   
10.         End If
    
11.     End If
    
12. '----------------------------------------------------
    
13.     If Target.Column = 6 Then
    
14.         ThisRow = Target.Row
    
15.         If Target.Value = "0" Then
    
16.             Range("F" & ThisRow).Value = "OK"
    
17.         ElseIf Target.Value = "1" Then
    
18.             Range("F" & ThisRow).Value = "NG"
    
19.         Else
    
20.         End If
    
21.     End If
    
22. '----------------------------------------------------
    
23.     If Target.Column = 7 Then
    
24.         ThisRow = Target.Row
    
25.         If Target.Value = "0" Then
    
26.             Range("G" & ThisRow).Value = "OK"
    
27.         ElseIf Target.Value = "1" Then
    
28.             Range("G" & ThisRow).Value = "NG"
    
29.         Else
    
30.         End If
    
31.     End If
    
32. '----------------------------------------------------
    
33.     If Target.Column = 8 Then
    
34.         ThisRow = Target.Row
    
35.         If Target.Value = "0" Then
    
36.             Range("H" & ThisRow).Value = "OK"
    
37.         ElseIf Target.Value = "1" Then
    
38.             Range("H" & ThisRow).Value = "NG"
    
39.         Else
    
40.         End If
    
41.     End If
    
42. '----------------------------------------------------
    
43.     If Target.Column = 9 Then
    
44.         ThisRow = Target.Row
    
45.         If Target.Value = "0" Then
    
46.             Range("I" & ThisRow).Value = "OK"
    
47.         ElseIf Target.Value = "1" Then
    
48.             Range("I" & ThisRow).Value = "NG"
    
49.         Else
    
50.         End If
    
51.     End If
    
52.    
    
53. End Sub

複製代碼

[attach]30743[/attach]
[attach]30744[/attach]

---

作者: 准提部林    時間: 2019-6-2 10:35

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim SelRng As Range
Set SelRng = Application.Intersect([E11:I36,E50:I74], Target)
If SelRng Is Nothing Then Exit Sub
If SelRng.Count > 1 Then Exit Sub
If SelRng & "" = "0" Then
   SelRng = "OK"
ElseIf SelRng & "" = "1" Then
   SelRng = "NG"
End If
End Sub


======================

---

作者: s13030029    時間: 2019-6-3 14:35

回復 3# 准提部林
感謝版主解答~還幫忙簡化程式~謝謝

---

歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)