时间戳

首页 > 解决方案 > 如何计算不同工作表上某些值的行数

excel - 如何计算不同工作表上某些值的行数

问题描述

我需要帮助。我需要计算包含与第二张表中 A 列相同的值的行。所以我需要从第一张表中遍历该列，并只计算那些与第二张表中的列具有相同值的行。如何使用 vba 代码做到这一点？

标签： excelvba

解决方案

尝试这个...

Sub Count_If_Equals()

    Dim Count As Integer
    Dim N_Of_Inputs As Integer
    Dim Bottom_Row_For_Comparing As Integer
    Dim DB_Bottom_Row_S1 As Integer
    Dim DB_Bottom_Row_S2 As Integer
    Dim DB_Start_Row As Byte
    Dim Pos As Integer
    Dim Name_Sheet1 As String
    Dim Name_Sheet2 As String
    Dim Col_To_Compare As Byte
    Dim Display_Result(1 To 2) As Byte
    
    'CONFIG
    DB_Start_Row = 5                 'in what row does the DB start?
    Col_To_Compare = 2            'in what column are the values to compare?
    Name_Sheet1 = "Sheet1"       'what is the name of the first sheet?
    Name_Sheet2 = "Sheet2"       'what is the name of the second sheet?
    Display_Result(1) = 3            'row to display result
    Display_Result(2) = 3            'column to display result
    
    'find bottom row of the DB in sheet 1
    DB_Bottom_Row_S1 = ThisWorkbook.Sheets(Name_Sheet1). _
    Cells(Rows.Count, Col_To_Compare).End(xlUp).Row
    'find bottom row of the DB in sheet 2
    DB_Bottom_Row_S2 = ThisWorkbook.Sheets(Name_Sheet2). _
    Cells(Rows.Count, Col_To_Compare).End(xlUp).Row
    
    Bottom_Row_For_Comparing = WorksheetFunction.Min(DB_Bottom_Row_S1, DB_Bottom_Row_S2)
    
    'compare and count
    For Pos = DB_Start_Row To Bottom_Row_For_Comparing
    
            If ThisWorkbook.Sheets("Sheet1").Cells(Pos, Col_To_Compare) = _
            ThisWorkbook.Sheets("Sheet2").Cells(Pos, Col_To_Compare) Then
            
                    Count = Count + 1
            
            End If
    
    Next Pos
    
'display the result
ThisWorkbook.Sheets("Sheet1").Cells(Display_Result(1), Display_Result(2)) = Count

End Sub

推荐阅读