Trying to reach address of cell with find method-CodePudding

I have 2 different sheet file on excel. I will try to find the equivalent of the date value I entered on the first page on the second page. I want to paste a block from the first cell, two cells to the right of the address of the value I found.

So I wrote a code block as below

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

But when debugging, I see that the value of my variable named "adres" is empty. The variable with the name "aranan" holds the date name. What is my mistakes?

CodePudding user response：

The "find all matches" logic is complex enough that it should be placed in a separate method - that makes your core logic simpler to manage.

For example:

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