VBA Excel - SQL datbase search looping

Hello,

New to the forums so apologies if i am in the wrong place.

Currently getting used to VBA inside excel and have been doing some tutorials and practices to get used to the code and have been trying to carry out a simple task without any success.

My task is to compare a range of cells and inside each cell inside this range get the string/text and assign it to a variable. then use this variable in a SQL statement to query a small database and return the result.

I have opted to do this with a for loop which gets the current cells text, assigns it to the variable and then executes the query and pops it into another cell, the problem is that outside out of the loop the code will run fine and i can change the name of the variable and everything is hunky dory, when inside the loop i get an error along the lines of

"RUN-TIME ERROR '3705':
Operation is not allowed when the object is open"

Sub NO6_Postcode()

'Connection Variables
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

' Ranges
Dim myRange As range
Dim nextUsed As range
Dim sqlQry As String
Dim myCell As range
Set myRange = range("A1:A10")
Set nextUsed = range("F1:F10")

'connection to the database via DSN
With cn
    .Provider = "MSDASQL"
    .ConnectionString = "DSN=localhostTest"
    .Open
    End With
    
For Each myCell In myRange

    sqlQry = myCell.Text
    
    Set cmd.ActiveConnection = cn
    cmd.CommandText = "SELECT * FROM test WHERE name LIKE  '" & sqlQry & "' "
    cmd.CommandType = adCmdText
    
    Set rs.Source = cmd
    rs.Open
    
    ActiveSheet.range("F1").CopyFromRecordset rs
    
Next myCell

End Sub

This is how i have written the code, ive looked in the forums here and on google for a solution but cant seem to find a similar case, the red text is to highlight were the debug always goes to.

All helps is appriciated thank you

    Set rs.Source = cmd
    rs.Open
    
    ActiveSheet.range("F1").CopyFromRecordset rs
    rs.Close

Also please read the forum rules on cross-posting.