利用VBA將搜尋內容顯示出來(數筆資料)

1^# 跳轉到 »

發表於 2017-4-17 14:34 | 顯示全部帖子

本帖最後由 yen956 於 2017-4-17 14:36 編輯

回復 2# bioleon69
試試看:

'搜尋
Private Sub CommandButton1_Click()
Dim eR As Long, Dt As Date, Dt1 As Date, Dt2 As Date, St As String, Rng As Range, E
If [E2] = "" And [E4] = "" Then Exit Sub
[G2].Resize(Rows.Count - 1, 3) = ""
eR = [A65536].End(xlUp).Row
Set Rng = [A2].Resize(eR - 1, 1)
If [E2] <> "" Then
Dt1 = [E2] & "/1"
Dt2 = Year(Dt1) & "/" & Month(Dt1) + 1 & "/1"
End If
For Each E In Rng
Dt = E: St = E.Offset(, 1)
If [E2] = "" Then
If St = [E4] Then
eR = [G65536].End(xlUp).Row + 1
E.Resize(1, 3).Copy Cells(eR, 7)
End If
ElseIf [E4] = "" Then
If Dt >= Dt1 And Dt < Dt2 Then
eR = [G65536].End(xlUp).Row + 1
E.Resize(1, 3).Copy Cells(eR, 7)
End If
Else
If Dt >= Dt1 And Dt < Dt2 And St = [E4] Then
eR = [G65536].End(xlUp).Row + 1
E.Resize(1, 3).Copy Cells(eR, 7)
End If
End If
Next
End Sub
'清除
Private Sub CommandButton2_Click()
[G2].Resize(Rows.Count - 1, 3) = ""
End Sub

複製代碼

靜思自在 : 為自己找藉口的人永遠不會進步。