工作簿函数—countif、vlookup


Sub chaxun()

Sheet1.Range("d20").ClearContents

  On Error Resume Next

    For i = 2 To Sheets.Count

        Sheet1.Range("d14") = Application.WorksheetFunction.VLookup(Range("d9"), Sheets(i).Range("a:j"), 5, 0)

        Sheet1.Range("d16") = Application.WorksheetFunction.VLookup(Range("d9"), Sheets(i).Range("a:j"), 6, 0)

        Sheet1.Range("d18") = Application.WorksheetFunction.VLookup(Range("d9"), Sheets(i).Range("a:j"), 3, 0)

        Sheet1.Range("d20") = Application.WorksheetFunction.VLookup(Range("d9"), Sheets(i).Range("a:j"), 8, 0)

        Sheet1.Range("d22") = Sheets(i).Name

        If Sheet1.Range("d20") <> "" Then

          Exit For

        End If

    Next

End Sub

Sub tongji()

    Dim i As Integer

    Dim k As Integer

    Dim x As Integer

    Dim y As Integer

    For i = 2 To Sheets.Count

        k = k + Application.WorksheetFunction.CountA(Sheets(i).Range("a:a"))-1

        x = x + Application.WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "男")-1

        y = y + Application.WorksheetFunction.CountIf(Sheets(i).Range("f:f"), "男")-1

    Next

    Sheet1.Range("d26") = k

    Sheet1.Range("d27") = x

      Sheet1.Range("d28") = y

End Sub

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容