excel vba 如果把二欄的資料copy到另二欄呢?
請問各位先進:我想要先把excel的二欄(B, C)資料copy到另二欄(E, F)，
然後，當數值1或數值2二欄(B, C)任儲存格有變更時，(E, F)欄儲值格內容會跟著改變。
我有想到使用 Worksheet_Change方式，但不知要怎麼寫vba
[attach]30046[/attach] 您好，試著寫了ㄧ個簡單的，歡迎高手指正
到VBA該工作表下
Private Sub Worksheet_Change(ByVal Target As Range)
Dim E As Long
E = WorksheetFunction.CountA(Range("B:B")) + 1
Range("E1:F" & E).Value = Range("B1:C" & E).Value
End Sub 直接E2輸入 =B2
再向下向右複製到F5
無須VBA是否更簡單 [b]回復 [url=http://forum.twbts.com/redirect.php?goto=findpost&pid=107280&ptid=21473]2#[/url] [i]a1234z[/i] [/b]
實際運行時，發現會當機，應該是觸發問題
修改如以下程式碼，就OK了
提供參考，如果有其他寫法，也請各位指教
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim E As Long
E = WorksheetFunction.CountA(Range("B:B")) + 1
Range("E1:F" & E).Value = Range("B1:C" & E).Value
Application.EnableEvents = True
End Sub [i=s] 本帖最後由 cody 於 2020-7-23 00:58 編輯 [/i]
參考看看
[b][size=6]module:[/size][/b][code]Sub copyBCtoEF()
With Worksheets("test1")
.Range("E:F").Value = .Range("B:C").Value
End With
End Sub[/code][code]Sub doUNDO()
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End Sub[/code][b][size=6]worksheet:[/size][/b][code]Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Row = 1 Then
doUNDO
MsgBox "不可更改標題"
GoTo tagExit
End If
If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count = 1 Then
With Worksheets("test1")
.Cells(Target.Row, Target.Column + 3).Value = .Cells(Target.Row, Target.Column).Value
End With
GoTo tagExit
End If
If (Target.Column = 2 Or Target.Column = 3) And (Target.Row >= 2 Or Target.Row >= 2) And Target.Count >= 2 Then
With Worksheets("test1")
.Range(.Cells(Target.Row, Target.Column + 3), .Cells(Target.Row + UBound(Target.Value2, 1) - 1, Target.Column + UBound(Target.Value2, 2) - 1 + 3)).Value = Target.Value2
End With
GoTo tagExit
End If
tagExit:
Application.EnableEvents = True
End Sub[/code]
頁:
[1]