Döngülü çözümü basitçe alttaki gibi yaptım ama bana döngüsüz lazım abeyyyyy.

Private Sub CommandButton1_Click()

    Dim con As Object

    Dim rs As Object

    Set con = CreateObject("Adodb.connection")

    Set rs = CreateObject("adodb.recordset")

    

    con.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""Excel 12.0;hdr=yes"""

    Sayfa2.[B:B].Clear

    For i = 2 To 7

        rs.Open "select * FROM [Sayfa1$] where aa='" & Sayfa2.Cells(i, 1).Value & "'", con, 1, 1

        On Error Resume Next

        Sayfa2.Cells(i, 2).Value = rs("bb")

        rs.movenext

        rs.Close

    Next

    On Error GoTo 0

    con.Close

    Set rs = Nothing

    Set con = Nothing

End Sub