Unanswered: run time error-91 object variable or with block variable not set
please help me out i am new for this field i have waiting replys
Dim activesheet As Collection
Dim totColumns, totRows, i, j, WDS_id
Dim prBatchName, prTableQry, inTableQry, prTableQry1, dbQry
Dim rs As New ADODB.Recordset
prBatchName = "tblProd_AGR_007"
-------------run time error here in this lines---------
totColumns = activesheet.Cells(2, 1).CurrentRegion.Columns.Count
totRows = activesheet.Cells(2, 1).CurrentRegion.Rows.Count
rs.Open "select * from " & prBatchName, dbconn, adOpenStatic, adLockOptimistic
For j = 3 To totRows
WDS_id = activesheet.Cells(j, 1)
rs.Find "WDS_ID=" & WDS_id
For i = 2 To totColumns
rs(activesheet.Cells(2, i).ID) = activesheet.Cells(j, i)
MsgBox "Data updated sucessfully"
ActiveSheet is an object/property of the Excel Application Object, hence it is confusing VBA
Just using the Key Word ActveSheet in Excel will reference the atcive sheet, sheet3 in the case, (the Application object is the default object and will be searched first for the the object/property if it is not qualified).
For your info the error message is saying you had not instantiated the activesheet collection object.
If you had used
Dim activesheet As New Collection
you would have recieved the 'Object doesn't supportthis property or method ... ' error message as the the properties of the sheet object are not available in a collection object.
Hope that makes sense.
One other comment, CurrentRegion as used here is redundant as the Cell range object can only have one row and column (as far as I know) !?