用EXCEL vba编程计算sheet1中值为20的单元格的个数

Question

用EXCEL vba编程计算sheet1中各行第1至10各单元格中值为20的单元格个数
结果分别输入sheet2的对应行的第一列中
谢谢

Answer 1

Public Sub Count()

Dim Row As Long
Dim Column As Long
Dim Counter As Long, countAll As Long
Dim rowCount As Long
rowCount = Worksheets("sheet1").Range("A1").CurrentRegion.EntireRow.Count

On Error Resume Next
Dim sht As Worksheet
Set sht = Worksheets("sheet2")
If Err.Number <> 0 Then
Err.Number = 0
Set sht = Worksheets.Add
sht.Name = "sheet2"
End If

For Row = 1 To rowCount
For Column = 1 To 10
If Worksheets("sheet1").Cells(Row, Column).Value = 20 Then
Counter = Counter + 1
End If
Next Column
sht.Cells(Row, 1).Value = Counter
countAll = countAll + Counter
Counter = 0
Next Row

sht.Cells(Row + 1, 1).Value = "共：" & CStr(countAll)

End Sub

Answer 2

Sub test()'循环嵌套循环
Dim rowi&, colx&, endrow As Integer, j As Integer, arr()
endrow = Sheet1.Cells(65536, 1).End(xlUp).Row
ReDim Preserve arr(1 To endrow)
For rowi = 1 To endrow'行循环
j = 0'初始化结果
For colx = 1 To 10'列循环
If Sheet1.Cells(rowi, colx) = 10 Then
j = j + 1'计算
End If
Next
arr(rowi) = j'结果输出
Next
Sheet2.Cells(1, 1).Resize(endrow, 1) = Application.Transpose(arr)
End Sub

Answer 3

Sub tt()
Dim i As Integer
For i = 1 To Sheet1.UsedRange.Rows.Count 'i赋值 走1 到sheet1有记录的行数
Sheet2.Cells(i, 1) = Application.WorksheetFunction.CountIf(Sheet1.Range("a" & i & ":j" & i), 20) '调用COUNTIF计算 用I 循环对应的sheet2的单元格的数值等于sheet1里面A i : j i 里面20的数量
Next i
End Sub本回答被提问者采纳

Answer 4

不用VBA都可以做到。在一处空白单元格中输入=countif（A1:H100,20）就可以了。追问

我想用VBA去实现，刚学VBA，先做这些基础的

追答

VBA不是万能的，能不用最好还是少用
在Sheet2A1中输入=countif（Sheet1!A1:j1,20）
有兴趣的话加Q1115332429