0

## Excel vba (Worksheet_SelectionChange + 條件變數加總問題)

(F列等同A列時，把D列數值加總於E列)

``````Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myData As Range, c As Range, cr1 As Range

st1 = Application.Match("code", Columns(1), 0)
ed1 = Application.Match("end", Columns(1), 0)
a1 = Application.Match("code", Rows(st1), 0)
b1 = Application.Match("date", Rows(st1), 0)
c1 = Application.Match("name", Rows(st1), 0)
d1 = Application.Match("qty", Rows(st1), 0)
e1 = Application.Match("total", Rows(st1), 0)
f1 = Application.Match("test", Rows(st1), 0)

Set cr1 = Range(Cells(st1, a1).Offset(1, 0), Cells(ed1, a1))
Set myData = Application.Intersect(Target, cr1)

If myData Is Nothing Then Exit Sub

For Each c In myData
If InStr(1, Cells(c.Row, a1).Value, "end") Then
Range(Cells(c.Row, c1), Cells(c.Row, f1)).ClearContents
ElseIf Cells(c.Row, a1).Value = "" Then
Rows(c.Row).ClearContents
ElseIf Cells(c.Row, a1) > 0 Then

Dim n1 As Integer
n1 = Cells(Rows.Count, a1).End(xlUp).Row
For i1 = st1 + 1 To n1
If Cells(i1, f1) = Cells(c.Row, a1) Then
qty = qty + Cells(i1, d1)
End If
Next

Cells(c.Row, e1) = qty
Else:
Cells(c.Row, e1) = ""
End If

Next

End Sub
``````

Else:

arhoi iT邦新手 5 級 ‧ 2018-07-13 10:08:56 檢舉

### 2 個回答

0
paicheng0111
iT邦高手 1 級 ‧ 2018-07-13 10:20:28

``````For i1 = st1 + 1 To n1
If Cells(i1, f1) = Cells(c.Row, a1) Then
qty = qty + Cells(i1, d1)
End If
Next i1
``````

``````qty = 0
For i1 = st1 + 1 To n1
If Cells(i1, f1) = Cells(c.Row, a1) Then
qty = qty + Cells(i1, d1)
End If
Next i1
``````

arhoi iT邦新手 5 級 ‧ 2018-07-13 10:26:56 檢舉

arhoi iT邦新手 5 級 ‧ 2018-07-13 11:17:31 檢舉

1

iT邦大神 1 級 ‧ 2018-07-13 09:49:41

``````If Target.Cells.Count > 1 Then Exit Sub
``````
arhoi iT邦新手 5 級 ‧ 2018-07-13 10:07:55 檢舉

arhoi iT邦新手 5 級 ‧ 2018-07-13 10:30:11 檢舉