各位先進大家好！因為之前對於VBA語法的學習興趣，所以想說應用在生活上。於是小弟我用sumif函數寫了一個加總點數的語法。語法如下
Sub Worksheet_Deactivate()
Dim a1, a2, a3, i As Integer
For i = 1 To 40
With Sheets("分配表")
a1 = Application.SumIf(Range("D3:D52"), i, Range("C3:C21"))
a2 = Application.SumIf(Range("h3:h52"), i, Range("g3:g52"))
a3 = Application.SumIf(Range("l3:l52"), i, Range("k3:k52"))
Sheets("統計表").Range("b" & i + 1).Value = a1 + a2 + a3
End With
Next i
End Sub
這語法是運行無誤的，但今天心血來潮，想把它做個大擴充，於是又增加了幾個工作表，又改了語法如下：
Sub Worksheet_Activate()
Dim a1, a2, a3, a4, a5, a6, a7, a8, a9, a10, a11, a12, i
For i = 1 To 22
With Sheets("班級幹部")
a1 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
a2 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
a3 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("掃地工作")
a4 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
a5 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
a6 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("每週工作")
a7 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
a8 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
a9 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
With Sheets("個人表現")
a10 = Application.SumIf(Range("D2:D23"), i, Range("C2:C23"))
a11 = Application.SumIf(Range("h2:h23"), i, Range("g2:g23"))
a12 = Application.SumIf(Range("l2:l23"), i, Range("k2:k23"))
End With
Sheets("統計結果").Range("b" & i + 1).Value = a1 + a2 + a3 + a4 + a5 + a6 + a7 + a8 + a9 + a10 + a11 + a12
Next i
End Sub
結果卻一動也不動，真是傷腦筋。我花了三、四個小時，即使腦筋打結，卻也摸不著頭緒。有勞各位了，可否告知小弟這語法的問題在哪裡？附上檔案！ |
發表於 2012-10-25 21:57
| 
發表於 2012-10-25 22:03
| 