Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

"Chip Pearson" wrote:

I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read, debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




"Mike" wrote in message
...
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

"Rick Rothstein (MVP - VB)" wrote:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick