用于计算列中值的 VBA 代码

VBA Code for counting Values in a column

提问by user3268372

Can anyone please tell me how to write VBA code for the following instance? I have data for 12 weeks for 8 students for their attendance on every week. I already wrote a code for filling the color of the cell with red in case of Absent and with green in case of Present.

谁能告诉我如何为以下实例编写 VBA 代码？我有 8 名学生每周出勤的 12 周数据。我已经编写了一个代码，用于在 Absent 的情况下用红色填充单元格的颜色，在 Present 的情况下用绿色填充。

Now I have to add number of "Absent" for every student in each row and fill that number in next column. Similarly count number of absents for individual student in each row and fill that number in another column. then finally, I should calculate the "present" percentage for each student.

现在我必须为每一行中的每个学生添加“缺席”的数量，并在下一列中填写该数字。类似地计算每一行中个别学生的缺席次数，并将该数字填入另一列。最后，我应该计算每个学生的“现在”百分比。

Option Explicit

Sub CopyPaste()
'
' CopyPaste Macro
'

    Sheets("Attendance").Select
    Range("A1:M9").Select
    Selection.Copy
    Sheets("Report").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Sub Filling()
'
' Filling Macro
'

    Dim Cell As Range
    Sheets("Report").Select
    For Each Cell In Range("B2:M9")
    If Cell.Value = "Present" Then
        Cell.Interior.Color = 255
    ElseIf Cell.Value = "Absent" Then
        Cell.Interior.Color = 5287936
    Else
        'do nothing
    End If
    Next Cell
End Sub

Sub Calculate()
'
' Calculate Macro
'
    Sheets("report").Select
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "Total Present"
    Range("N2").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("N3").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("N4").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("N5").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("N6").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("N7").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("N8").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("N9").Select
    ActiveCell.FormulaR1C1 = "7"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Total Absent"
    Range("O2").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("O3").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("O4").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("O5").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("O6").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("O7").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("O8").Select
    ActiveCell.FormulaR1C1 = "8"
    Range("O9").Select
    ActiveCell.FormulaR1C1 = "5"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Attendance Rate"
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=(RC[-2]*100)/12"
    Range("P2").Select
    Selection.AutoFill Destination:=Range("P2:P9"), Type:=xlFillDefault
    Range("P2:P9").Select
End Sub

回答by Floris

Use this for inspiration:

使用此作为灵感：

Sub countAbsence()
Dim r As Range
Dim c
Set r = Sheets("Sheet1").Range("A1:A8")
c = Application.WorksheetFunction.CountIf(r, "yes")
MsgBox "column A has " & c & " instances of 'yes'"
End Sub

Ask if you need more explanation.

询问您是否需要更多解释。

If you want to put the count somewhere, and the percentage, you could do:

如果你想把计数和百分比放在某个地方，你可以这样做：

Range("B1").value = c
Range("C1").value = c / 8
Range("C1").style = "percent"

updatecomplete code to take the table in A2:M9and generate columns of 'present', 'absent' , and 'attendance rate':

更新完整代码以获取表格A2:M9并生成 'present'、'absent' 和 'attendance rate' 列：

Option Explicit
Option Compare Text

Sub calculcatePresentAbsent()
  Dim absent, present
  Dim i
  Dim record As Range, target As Range

  Set record = Range("B1:M1")
  Set target = Range("N1")
  target.Value = "present"
  target.Offset(0, 1).Value = "absent"
  target.Offset(0, 2).Value = "attendance rate"

  For i = 1 To 8 ' loop over students
    Set record = record.Offset(1, 0)  ' go to next row
    Set target = target.Offset(1, 0)
    absent = Application.WorksheetFunction.CountIf(record, "Absent")
    present = Application.WorksheetFunction.CountIf(record, "Present")
    target.Value = present
    target.Offset(0, 1).Value = absent
    target.Offset(0, 2).Value = present / (present + absent)
    target.Offset(0, 2).Style = "percent"
  Next

End Sub

This basically does everything your posted code does - except it does it "dynamically", instead of with hard coded numbers.

这基本上可以完成您发布的代码所做的一切 - 除了它是“动态”执行的，而不是使用硬编码的数字。

Good luck with your class.

祝你上课顺利。