时间戳

首页 > 解决方案 > 查找平均输入框 VBA

vba - 查找平均输入框 VBA

问题描述

我在 L 列中有很多成绩。我想要一个输入框，然后我想为 A 列中的一个学生写学生 ID。我想知道是否有一种方法可以计算所有分数的平均值仅使用 student_id 的 student_id 学生的成绩？像这样的东西，但这不起作用：

 Dim StudentID As String
 Dim LastRow As Long
 Dim cell As Range
 Dim sum As Long
 Dim RowCount As Long



 StudentID = InputBox("Please enter a Student_ID:")
 LastRow = Cells(Rows.count, "A").End(xlUp).Row

 sum = 0
 RowCount = 0

 For Each cell In Range("A:A2" & LastRow)
 If cell.Value = StudentID Then 
 sum = sum + Cells(cell.Row, 12)
 RowCount = RowCount + 1
 Next cell

 avg = sum / RowCount
 MsgBox avg

标签： vbaexcel

解决方案

您的代码中只是有一些语法错误。

Sub GetStudentAverage()
    Dim StudentID As String
    Dim LastRow As Long
    Dim cell As Range
    Dim sum As Long
    Dim RowCount As Long

    StudentID = InputBox("Please enter a Student_ID:")
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row

    sum = 0
    RowCount = 0

    For Each cell In Range("A2:A" & LastRow)
        If cell.Value = StudentID and IsNumeric(Cells(cell.Row, 12)) Then
            sum = sum + Cells(cell.Row, 12)
            RowCount = RowCount + 1
        End If
    Next cell

    If RowCount > 0 Then
        avg = sum / RowCount
    Else
        avg = "No Matches Found!"
    End If
    MsgBox avg
End Sub

你错过了一个End If并且范围不正确。此外，为了避免除以 0，只需简短检查一下行数是否 > 0。

推荐阅读