如何对不包含特定值的单元格求和

Question

如果Excel中第一张工作表中的行与第二张工作表上的行不同，我将尝试对它们进行计数。

但是有问题。

inicijaliDT是我在Sheet2上检查数据的范围。

inicijali = wsNOSTROSheet.Range("R" & brojac).Value
For Each Row In inicijaliDT
If inicijali <> Row.Value Then
brojRedaka = brojRedaka + 1
'Else
'Exit For
End If
Exit For
Next Row

Answer 1

如果我正确理解了您想要完成的任务，请尝试下一个代码：

Sub testCompareRows()
Dim sh1 As Worksheet, sh2 As Worksheet, lastR As Long, i As Long, cnt As Long

Set sh1 = ActiveSheet 'use here your first sheet
Set sh2 = sh1.Next    'use here your second sheet
lastR = sh1.UsedRange.rows.count + sh1.UsedRange.row - 1
For i = 1 To lastR
With Application
If Join(.Transpose(.Transpose(sh1.rows(i)))) <> _
Join(.Transpose(.Transpose(sh2.rows(i)))) Then cnt = cnt + 1
End With
Next i
If cnt > 0 Then MsgBox "There are " & cnt & " different rows..."
End Sub

您没有问澄清问题，代码假设两张图纸的行数相同，第一张图纸中的每个行数都要与第二张图纸中相同的行数进行比较。。。

Answer 2

计数不相等

如果inicijaliDT是一列范围，则可以执行以下操作之一：

Sub testLoop()
Dim cel As Range
Dim brojRedaka As Long
inicijali = wsNOSTROSheet.Range("R" & brojac).Value
For Each cel In inicijaliDT.Cells
If cel.Value <> inicijali Then
brojRedaka = brojRedaka + 1
End If
Next cel
'Debug.Print brojRedaka
End Sub
Sub testCountIf()
Dim brojRedaka As Long
inicijali = wsNOSTROSheet.Range("R" & brojac).Value
brojRedaka = inicijaliDT.Rows.Count _
- Application.CountIf(inicijaliDT, inicijali)
'Debug.Print brojRedaka
End Sub

如果inicijaliDT是多列范围，并且您正在尝试计算在其任何单元格中都找不到inicijali的行数，则可以执行以下操作之一：

Sub testRowLoop()
Dim RowRange As Range
Dim cel As Range
Dim brojRedaka As Long
Dim isFound As Boolean
inicijali = wsNOSTROSheet.Range("R" & brojac).Value
For Each RowRange In inicijaliDT.Rows
For Each cel In RowRange.Cells
If cel.Value = inicijali Then
isFound = True
Exit For
End If
Next cel
If isFound Then
isFound = False
Else
brojRedaka = brojRedaka + 1
End If
Next RowRange
'Debug.Print brojRedaka
End Sub
Sub testRowMatch()
Dim RowRange As Range
Dim brojRedaka As Long
inicijali = wsNOSTROSheet.Range("R" & brojac).Value
For Each RowRange In inicijaliDT.Rows
If IsError(Application.Match(inicijali, RowRange, 0)) Then
brojRedaka = brojRedaka + 1
End If
Next RowRange
' Debug.Print brojRedaka
End Sub

Answer 3

这个你可以自定义。记住在运行代码之前要配置代码中的值。

'TASK: count rows in first sheet that 
'have a different value to the same cells in the second sheet.
Sub countRows()
Dim positioner As Long, count As Long, last_row As Long
Dim db_first_row As Byte, col_n As Byte, name_s1 As String
Dim name_s2 As String, display_ans As String, msg As String
Dim display_in_cell As Boolean, display_in_msgbox As Boolean
'CONFIR HERE BEFORE YOU RUN IT
'------------------------------------------------
name_s1 = "Sheet1"         'name of first sheet
name_s2 = "Sheet2"         'name of second sheet
db_first_row = 2           'first row of the data set
col_n = 2                  'colum number of the data set
count = 0                  'from what number would you like to start counting?
display_in_cell = True     'do you want to display the answer in a specific cell?
display_ans = "C3"         'in what cell?
display_in_msgbox = True   'do you want to display the answer in a msgbox?
msg = "Different values counted: " 'message to display in msgbox
'------------------------------------------------
'get the last row of the data set
last_row = Sheets(name_s1).Cells(Rows.count, col_n).End(xlUp).Row
For positioner = db_first_row To last_row

'if values of both sheets match in the same cell then count
If Sheets(name_s1).Cells(positioner, col_n) <> _
Sheets(name_s2).Cells(positioner, col_n) Then
count = count + 1
End If
Next positioner
If display_in_cell Then
Sheets(name_s1).Range(display_ans) = count
End If
If display_in_msgbox Then
MsgBox msg & count
End If
End Sub