Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
'↑如果觸發編輯儲存格位址是 ""$A$2""
If IsNumeric([A1]) And IsNumeric([A2]) And [A2] <> "" Then
'↑如果IsNumeric()判斷[A1]是數值,IsNumeric()判斷[A2]也數值,而且[A2]不是空字元
[A3] = [A1] + [A2]: [A2] = ""
'↑令[A3]儲存格值=[A1]儲存格值+[A2]儲存格值: 然後令[A2]儲存格值是空字元
End If
End If
End Sub作者: coafort 時間: 2023-2-16 13:23
回復 coafort
謝謝前輩發表此主題
後學建議vba觸發的方式
Option Explicit
Private Sub Wo ...
Andy2483 發表於 2023-2-16 11:42
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
'↑如果觸發編輯儲存格位址是 ""$A$2""
If IsNumeric([A1]) And IsNumeric([A2]) Then
'↑如果IsNumeric()判斷[A1]是數值,IsNumeric()判斷[A2]也是數值
Application.EnableEvents = False
'↑令觸發失效
[A1] = [A1] + [A2]: [A2] = ""
'↑令[A1]儲存格值=[A1]儲存格值+[A2]儲存格值: 然後令[A2]儲存格值是空字元
Application.EnableEvents = True
'↑令恢復觸發
End If
End If
End Sub作者: coafort 時間: 2023-2-16 18:33
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
'↑如果觸發編輯儲存格位址是 ""$A$2""
If IsNumeric([A1]) And IsNumeric([A2]) Then
'↑如果IsNumeric()判斷[A1]是數值,IsNumeric()判斷[A2]也是數值
Application.EnableEvents = False
'↑令觸發失效
[A1] = [A1] + [A2]: [A2] = ""
'↑令[A1]儲存格值=[A1]儲存格值+[A2]儲存格值: 然後令[A2]儲存格值是空字元
Application.EnableEvents = True
'↑令恢復觸發
End If
End If
If Target.Address = "$B$2" Then
'↑如果觸發編輯儲存格位址是 ""$B$2""
If IsNumeric([B1]) And IsNumeric([B2]) Then
Application.EnableEvents = False
[B1] = [B1] + [B2]: [B2] = ""
Application.EnableEvents = True
End If
End If
If Target.Address = "$C$2" Then
'↑如果觸發編輯儲存格位址是 ""$C$2""
If IsNumeric([C1]) And IsNumeric([C2]) Then
Application.EnableEvents = False
[C1] = [C1] + [C2]: [C2] = ""
Application.EnableEvents = True
End If
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = "" Then Exit Sub
If Not Intersect(Range("a3:c3"), .Cells) Is Nothing Then
.Cells(-1, 1) = Val(.Cells(-1, 1)) + Val(.Value)
.ClearContents
End If
End With
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'↑以下是關於儲存格編輯觸發的程序
If .Count > 1 Or .Item(1) = "" Then Exit Sub
'↑如果觸發格數量大於 1 或觸發格空白!就結束程式執行
If Not Intersect(Range("a2:c2"), .Cells) Is Nothing Then
'↑如果觸發格是在[A2:A2]儲存格裡??
.Cells(0, 1) = Val(.Cells(0, 1)) + Val(.Value)
'↑令觸發格的上方那格的值 + (觸發格值以Val 函數轉化回傳的數值)
.ClearContents
'↑清除觸發格的內容
'此清除的程序再次的觸發了,但是 .Item(1) = "" 所以結束程式執行
End If
End With
End Sub作者: Andy2483 時間: 2023-2-18 08:42
建議使用 准提部林前輩的方法更方便擴充使用該功能的儲存格
在以下程式碼藍字部位修改,即可擴充或變更
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = "" Then Exit Sub
If Not Intersect([A2:C2,G2:I2], .Cells) Is Nothing Then
.Cells(0, 1) = Val(.Cells(0, 1)) + Val(.Value)
.ClearContents
End If
End With
End Sub作者: coafort 時間: 2023-2-21 16:49
本帖最後由 coafort 於 2023-2-21 16:53 編輯
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = " ...
准提部林 發表於 2023-2-17 10:48
我改成這樣
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Count > 1 Or .Item(1) = "" Then Exit Sub
If Not Intersect([AQ212:AQ218,AW212:AW218,BB221], .Cells) Is Nothing Then
.Cells(0, 1) = Val(.Cells(0, 1)) + Val(.Value)
.ClearContents
End If
End With
End Sub
但是出錯了
請問要怎麼改呢?
謝謝大大作者: Andy2483 時間: 2023-2-22 07:35
謝謝前輩一起學習
請前輩再測試看看,謝謝
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
'↑以下是關於儲存格編輯觸發的程序
If .Count > 1 Or .Item(1) = "" Then Exit Sub
'↑如果觸發格數量大於 1 或觸發格的下方格是空白!就結束程式執行
If Not Intersect([AQ212:AQ218,AW212:AW218,BB221], .Cells) Is Nothing Then
'↑如果觸發格是在[A2:A2]儲存格裡??
.Cells(1, 2) = Val(.Cells(1, 2)) + Val(.Cells(1, 1))
'↑令觸發格的右邊那1格的值 + (觸發格值以Val 函數轉化回傳的數值)