Runtime 438 Error - Object Doesn't support this property method

[E_DPSG]

Hi everyone -

Running into an issue on assigning the value to the cell with the following statement:

Sheets("DASHBOARD").RangeR1C1(RowPos + i, StartPos + i).Value = rs(i)

I am calling a SQL statement to fetch the data to post against the dashboard. The SQL statement has been simplified for troubleshooting, but ultimately the worksheet is being populated via a db connection - so the end users can browse the source data for the dashboard.


here is the complete code:

Sub FetchData_QRY(ByRef ReferenceNum As String, ByRef NumFields As Integer, ByRef StartPos As Integer)
 Dim cn As Object, rs As Object, sql As String, RowPos As Integer, i As Integer
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=" & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
        .Open
    End With
    
    RowPos = 18 ' starting line defaulted
    
    sql = "SELECT * FROM [Sheet1$] WHERE Col1 = '" & ReferenceNum & "'"
    Set rs = cn.Execute(sql)
    
    Do
        'With Sheets("DASHBOARD")
            For i = 0 To NumFields
                Sheets("DASHBOARD").RangeR1C1(RowPos + i, StartPos + i).Value = rs(i)
            Next i
        'End With
        RowPos = RowPos + 1
        rs.MoveNext
    Loop Until rs.EOF
        
    '---Clean up---
    rs.Close
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

Any thoughts on how to correct?

 

[B___P]

Hi E_DPSG
try this

Sheets("DASHBOARD").cells(RowPos + i, StartPos + i).Value = rs(i)

hope this helps

 

[E_DPSG]

That worked.... I need to sleep. Thanks for the help!