本帖最后由 ioi_chan 于 2017-5-27 12:19 编辑
請問利用vba, 在B欄中任意一格輸入CCC後, 除了自身有CCC的這一格外,會自動檢測該格的字元與其他B欄的字元交換位置,請看附件。 感謝各位老師的幫忙,謝謝!
代码如下 :
- Dim Ad
- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Application.EnableEvents = False
- If Target.Column <> 2 Or Target.Count > 1 Then Exit Sub
- Ad = Target
- Application.EnableEvents = True
- End Sub
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Target.Column <> 2 Or Target.Count > 1 Then Exit Sub
- Application.EnableEvents = False
- Dim y, ar, i
- y = Range("B65536").End(3).Row
- ar = Range("a1:b" & y)
- For i = 1 To UBound(ar)
- If i <> Target.Row Then
- If Target = ar(i, 2) Then
- Cells(i, 2) = Ad
- End If
- End If
- Next i
- Application.EnableEvents = True
- End Sub
复制代码
|