Sub calc()
Dim name As String
Dim nameCol As String '姓名所在列
Dim scoreCol As String '成绩所在列
Dim cellNameValue As String
Dim cellScoreValue As String
Dim iNameCol As Integer
Dim iScoreCol As Integer
Dim i As Long
Dim totalScore As Double
name = InputBox("请输入要查找的用户", "输入用户")
nameCol = InputBox("请输入姓名所在列", "输入姓名列")
scoreCol = InputBox("请输入分数所在列", "输入分数列")
If name = "" Then
MsgBox ("用户列不能为空")
Return
End If
If nameCol = "" Then
MsgBox ("姓名列不能为空")
Return
End If
If scoreCol = "" Then
MsgBox ("分数列不能为空")
Return
End If
' 65536 '0fffice 最大行
iNameCol = Columns(nameCol).Column
iScoreCol = Columns(scoreCol).Column
For i = 1 To 65536
cellNameValue = ActiveSheet.Cells(i, iNameCol)
cellScoreValue = ActiveSheet.Cells(i, iScoreCol)
If InStr(cellNameValue, name) > 0 Then
If IsNumeric(cellScoreValue) Then
totalScore = totalScore + cellScoreValue
End If
End If
Next
MsgBox (name + "的总分是" + Str(totalScore))
End Sub