HELP "Object Required"

[tatendatiffany]

Hi,
could some one help me i am facing a problem on my code "object required" but i have specified in the function what could i have done wrong: the line higlighted in bold is where i am getting the error but i thought i had specified in the function the recordset??

 Function getrecordset(str) As String
    'create recordset object
     Dim rs As ADODB.Recordset
     Set rs = New ADODB.Recordset
     With rs
    .ActiveConnection = cn
    'using the str variable open the recordset
    .Source = str
    .Open
     End With
    'if recordset NOT NULL
     If Not (rs.BOF And rs.EOF) Then
    getrecordset = rs(0)
        'getrecordset = recordset
    'else
        'getrecordset = "No Result"
    getrecordset = vbNullString
    'end if
                                'close and destroy recordset object
     Set rs = Nothing
End Function
Sub getresults()
    Dim r As Integer                    'row counter
    Dim c As Integer                    'column counter
    Dim str As String
    Dim rs As Recordset
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    With cn
       .ConnectionString = "Password=" & password & ";Persist Security Info=True;User ID=" & username & ";Data Source=" & DatabaseEnv & ";Mode=ReadWrite;"   'Assumption based on your connection string being valid
       .Provider = "IBMDADB2.DB2COPY1"      'assumption based on your provider being correct
       ' .Open                       'if the above are true then this will open DB
    'End With
    
   [B]Set rs = Db.OpenRecordset
[/B]    For c = 31 To 35                 'columns AE to AI
        For r = 2 To 39                             'start with first row
            If Cells(r, c).Value <> "" Then            'is there a value in the row?
                str = Cells(r, c).Value                 'this is the SQL string we need for the recordset
                Cells(r, c - 5).Value = getrecordset(str) 'use a function to open the recordset and return the value to column 3
            End If
        Next r
    Next c                              'next column
End With
End Sub

 

[tatendatiffany]

ok so in the str string thats were i need to create the recordset ok i think it make sense now.
I am just going to do something like a vlookup of the columns..
e.g,

str= application. vlookup (ranges("environ3") & sheets ("index").range )A2:A39

this is just a rough idea something like this should work right as its telling excel to go into the sheet and it then picks up the queries in the excel sheet

Thank you again for you patience and help

 

[tatendatiffany]

Could someone help me I need to create a recordset for my function but now i am getting and error invliad use of property

[B] rs = "select * from worksheet" & Range("Environ1") & ("Environ3") & ("Environ4") & ("Environ5") & ("Environ7")[/B]
   Set rs = Db.OpenRecordset

I have added rs that tell excel to go into the sheet ad run the strings

 

[tatendatiffany]

Resolved

I am going to start a new thread as i am facing a different issue now

thank you for all your help