杨老师课堂之Excel VBA 程序开发第八讲使用工作表函数

 Sub tongji()
Dim i, k, l, m As Integer
 
For i = 2 To Sheets.Count
    k = k + Application.WorksheetFunction.CountA(Sheets(i).Range("a:a")) - 1'不算表头
    l = l + Application.WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "男")
    m = m + Application.WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "女")
Next
 
Sheet1.Range("d26") = k
Sheet1.Range("d27") = l
Sheet1.Range("d28") = m
 
 
End Sub
 
-------------------------------------------------------------------------------------------------------------
 
Sub chaxun()
 
 
On Error Resume Next'错误回避（遇到错误，跳过，继续执行下一行）
 
Sheet1.Range("d14").ClearContents'对Sheet1表中D14单元格进行清除内容
 
 
 
For i = 2 To Sheets.Count
    Sheet1.Range("d14") = Application.WorksheetFunction.VLookup(Sheet1.Range("d9"), Sheets(i).Range("a:h"), 5, 0)
    Sheet1.Range("d16") = Application.WorksheetFunction.VLookup(Sheet1.Range("d9"), Sheets(i).Range("a:h"), 6, 0)
    Sheet1.Range("d18") = Application.WorksheetFunction.VLookup(Sheet1.Range("d9"), Sheets(i).Range("a:h"), 3, 0)
    Sheet1.Range("d20") = Application.WorksheetFunction.VLookup(Sheet1.Range("d9"), Sheets(i).Range("a:h"), 8, 0)
    Sheet1.Range("d22") = Sheets(i).Name
    
    If Sheet1.Range("d14") <> "" Then
        Exit For
    End If
Next
End Sub