时间戳

首页 > 解决方案 > 计数并转移到新工作表 VBA

vba - 计数并转移到新工作表 VBA

问题描述

我需要找到每个学习板的学生人数，我在 H 列中有每个学习板的所有数字，所以我的代码现在只计算每个数字出现的次数。我想知道是否有更简单的方法可以做到这一点，因为你可以看到我的代码很长。我还需要将学生人数转移到一个名为统计的新工作表中。希望有人可以帮助

Sub countstudents()
Range("V2") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "1")
Range("V7") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "22")
Range("V8") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "23")
Range("V9") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "24")
Range("V10") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "25")
Range("V11") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "26")
Range("V12") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "28")
Range("V13") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "29")
Range("V14") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "31")
Range("V15") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "32")
Range("V16") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "33")
Range("V17") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "34")
Range("V18") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "35")
Range("V19") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "36")
Range("V20") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "41")
Range("V21") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "81")
Range("V22") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "121")
Range("V23") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "122")
Range("V24") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "163")
Range("V25") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "164")
Range("V26") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "183")
Range("V27") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "184")
Range("V28") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "185")
Range("V29") = Application.WorksheetFunction.CountIf(Range("H2:H18288"), "")

标签： vbaexcel

解决方案

这应该可以解决问题：

首先，我们构建一个二维数组，其中包含范围公式中的所有行号，以及您的条件Countif()

我们迭代该数组以填充工作表单元格。需要注意的重要一点是，明确引用需要粘贴的工作簿和工作表是明智的。所以例如Workbooks("book1.xlsx").worksheets("sheet1").Range() =

Sub countstudents()
Dim i as long
Dim j as long
dim k as long
Dim arr(1 to 24, 1 to 2) as string
    arr(1, 1) = 2
    arr(1, 2) = 1
    arr(24, 1) = 29
    arr(24, 2) = ""
    For i is 2 to 23
      For j is 1 to 2
         If j = 1 then 
            arr1(i,j) = 5 + i 'starts at 7 and goes till 28
         Else 
            arr1(i,j) = 20 + i 'starts at 22 and goes till 43
         End if
      next j
    next i 

For k = 1 to ubound(arr)
    Range("v"&arr(k,1)) = application.worksheetfunction.countif(Range("H2:H18288"), arr(k,2))
next k
end sub

推荐阅读