if 多层嵌套,excel区域多条件筛选并计数
想对产品报告单进行分析,公差上限-下限<=0.02的进行判定并计数,最后输出合格个数与不合格个数,乱写了一个,但是不对,请大神帮忙看看,谢谢@Sub 宏1()
'
' 宏1 Macro
'
Dim i, j, s, k, m, n, t As Integer '定义
For i = 3 To Range("B65536").End(xlUp).Row '从B列第三行到最后一行
If Cells(i, "B") - Cells(i, "C") > 0 And Cells(i, "B") - Cells(i, "C") <= 0.02 Then '如果公差在0.02以内,开始判断每行的数据
For j = 4 To Range("IV" & i).End(xlToLeft).Column '每一行从D列到最后一列进行判断
t = Mid(Cells(i, j), i, 1)
For s = 1 To Len(Cells(i, j))
k = Asc(Mid(s, 1, 1))
If IsNumeric(Cells(i, j)) Then '判断为数字
If Cells(i, j) >= Cells(i, "A") + Cells(i, "C") And Cells(i, j) <= Cells(i, "A") + Cells(i, "B") Then '若在范围内
m = m + 1 '合格数+1
Else
n = n + 1 'NG数+1
End If
ElseIf Cells(i, j) = "OK" Or Cells(i, j) = "NG" Then
j = 4
i = i + 1
ElseIf IsEmpty(Cells(i, j)) Then '判断为空
ElseIf k > 0 And k < 255 Then '判断为字母
ElseIf Asc(t) < 0 Then '判断为汉字If Asc(t) < 0
ElseIf Cells(i, j) = "OK" Or Cells(i, j) = "NG" Then
j = 4
i = i + 1
Else
End If
j = j + 1
Else: j = 4 And i = i + 1
End If
Next i
Range("A1") = m
Range("B1") = n
End Sub