Question

excel VBA，sheet1有很多类似的数据，我想通过三个特定的条件把sheet1中所有行数据找出来显示在shheet2

Private Sub CommandButton1_Click()
Range("A20:P100").ClearContents
i = 2
j = 20
date1 = Format(Cells(1, 1), "YYYYMMDD")
date2 = Format(Cells(2, 1), "hh:mm:ss")
Money = Cells(3, 1)
With Sheets("Sheet1")
Do Until .Cells(i, 4) = ""
aa = Right(.Cells(i, 4), 8)
If date1 = aa Then
bb = Format(.Cells(i, 5), "hh:mm:ss")
Dim chazhi As Integer
cc = Val(.Cells(i, 6))
dd = Val(Money)
chazhi = Val(DateDiff("n", date2, bb))
jine = Val(dd - cc)
If -5 < chazhi And chazhi < 5 And -2 < jine And jine < 2 Then
For n = 1 To 15
Cells(j, n) = .Cells(i, n)
Next n
j = j + 1
End If
End If
i = i + 1
Loop
End With
End Sub
这个怎么查找不出来

Answer 1

计算日期差值错了。
Private Sub CommandButton1_Click()
Range("A20:P100").ClearContents
i = 2
j = 20
date1 = Format(Cells(1, 1), "YYYYMMDD")
date2 = Format(Cells(2, 1), "hh:mm:ss")
Money = Cells(3, 1)
With Sheets("Sheet1")
Do Until .Cells(i, 4) = ""
aa = Right(.Cells(i, 4), 8)
If date1 = aa Then
bb = Format(.Cells(i, 5), "hh:mm:ss")
Dim chazhi As Integer
cc = Val(.Cells(i, 6))
dd = Val(Money)
chazhi = Val(DateDiff("d", date2, bb))
jine = Val(dd - cc)
If (chazhi > -5 And chazhi < 5) And (jine > -2 And jine < 2 ) Then
For n = 1 To 15
Cells(j, n) = .Cells(i, n)
Next n
j = j + 1
End If
End If
i = i + 1
Loop
End With
End Sub

追问

我要按照分钟误差来查找，不是按照天