我在 excel 上有 2 個不同的作業表檔案。我將嘗試找到與我在第二頁的第一頁上輸入的日期值相同的日期值。我想從第一個單元格粘貼一個塊，在我找到的值的地址右側有兩個單元格。
所以我寫了一個代碼塊如下
Dim aranan As Date
Dim firstAddress As String
Dim adres As Range
Dim c As Range
Private Sub CommandButton2_Click()
aranan = Range("B1").Value
Range("B2:G6").Select
Selection.Copy
With Worksheets(3).Range("A1:A500")
Set adres = Range("A1:A100").Find(aranan, LookAt:=xlWhole, MatchCase:=True)
If Not adres Is Nothing Then
firstAddress = adres.Address
Do
ActiveSheet.Paste Destination:=Worksheets(2).Range("C1:H5")
Loop While Not adres Is Nothing
End If
End With
End Sub
但是在除錯時，我看到名為“adres”的變數的值為空。名稱為“aranan”的變數保存日期名稱。我的錯誤是什么？
uj5u.com熱心網友回復：
“查找所有匹配項”邏輯足夠復雜，應將其放置在單獨的方法中 - 這使您的核心邏輯更易于管理。
例如：
Private Sub CommandButton2_Click()
Dim dt As Date, col As Collection, c As Range
dt = Me.Range("B1").Value 'Me = the worksheet for this code module
Set col = FindAll(Worksheets(3).Range("A1:A500"), dt)
If col.Count > 0 Then
For Each c In col
Me.Range("B2:G6").Copy c.Offset(0, 2) 'two columns over
Next c
Else
MsgBox "No matches found"
End If
End Sub
'find all matches for `val` in a range, and return as a collection
Public Function FindAll(rng As Range, val) As Collection
Dim rv As New Collection, f As Range, addr As String
Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.CountLarge), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not f Is Nothing Then addr = f.Address() 'first cell found
Do Until f Is Nothing
rv.Add f
Set f = rng.FindNext(after:=f)
If f.Address() = addr Then Exit Do 'exit if we've wrapped back to the start
Loop
Set FindAll = rv
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/314255.html
上一篇：VBA檔案保存路徑問題