EXCEL VBA

Private Sub Worksheet_Activate()


MsgBox "歡迎進入保險系統"



總合

平均

筆數



End Sub

Sub 總合()


Dim x, y, z

x = 0

For y = 1 To Sheets.Count - 1



z = Sheets(y).Range("c1").Value

Sheets(y).Range("c1").FormulaR1C1 = "=SUM(C[-1])"



x = Sheets(y).Range("c1").Value + x

Sheets(y).Range("c1").Value = z

Next

Sheets(Sheets.Count).Range("c1").Value = x



End Sub

Sub 平均()

Dim x, y

x = 0

For y = 1 To Sheets.Count - 1



z = Sheets(y).Range("c1").Value



Sheets(y).Range("c1").FormulaR1C1 = "=average(C[-1])"



x = Sheets(y).Range("c1").Value + x



Sheets(y).Range("c1").Value = z

Next

Sheets(Sheets.Count).Range("c2").Value = x / y



End Sub



Sub 筆數()

Dim x, y

x = 0

For y = 1 To Sheets.Count - 1



z = Sheets(y).Range("c1").Value



Sheets(y).Range("c1").FormulaR1C1 = "=count(C[-1])"



x = Sheets(y).Range("c1").Value + x



Sheets(y).Range("c1").Value = z

Next

Sheets(Sheets.Count).Range("c3").Value = x



End Sub



Sub 最大值()

Dim x, y, z, x1

x1 = 0



For y = 1 To Sheets.Count - 1



z = Sheets(y).Range("c1").Value



Sheets(y).Range("c1").FormulaR1C1 = "=max(C[-1])"



x = Sheets(y).Range("c1").Value



If x > x1 Then

x1 = x

Else

x1 = x1



End If





Sheets(y).Range("c1").Value = z

Next

Sheets(Sheets.Count).Range("c4").Value = x



End Sub



Sub 最小值()

Dim x, y, z, x1



Sheets(1).Range("c1").FormulaR1C1 = "=min(C[-1])"

x1 = Sheets(1).Range("c1").Value

For y = 1 To Sheets.Count - 1



z = Sheets(y).Range("c1").Value



Sheets(y).Range("c1").FormulaR1C1 = "=min(C[-1])"



x = Sheets(y).Range("c1").Value



If x < x1 Then

x1 = x

Else

x1 = x1



End If





Sheets(y).Range("c1").Value = z

Next

Sheets(Sheets.Count).Range("c4").Value = x



End Sub