Hello I am writing a vba macro for my custom need. Below is a code line in this macro: Worksheets(strt_stmp).Range(Cells(rw1, cl1), Cells(rw1 + 8, cl1)).Value = ' (strt_stmp, rw1, cl1 are variables defined earlier) The intent here is to clear the cell contents in the range. I am facing a strange scenario. Sometimes when I run the macro, it goes through without any problem. However sometimes when I run it, I get Run-time error 1004: Application-defined or object-defined error. Monikers are used by OLE to connect to and activate OLE objects. Method in the OLEObject class does this by dispatching the method call to the real OLE object. Please help me in understanding the problem here. Thank You in advance. Regards, Pawan. When you get the error again, go into DEBUG and determine the values for the variables involved and ensure that they are all legitimate values. You may find that one is not valid - for example, rw1 might be zero, or it might be so large that rw1+8 is greater than the number of rows permitted for the worksheet. It could also be that the active workbook doesn't contain a sheet named as whatever is in strt_stmp. Your statement doesn't actually totally clear the contents of the cells, it places a zero length string into them. To actually clear the contents you might consider using Worksheets(strt_stmp).Range(Cells(rw1, cl1), Cells(rw1 + 8, cl1)).ClearContents Another possibility might be that the worksheet is protected which would prevent the change to the values in those cells if they are locked. I am free because I know that I alone am morally responsible for everything I do. Hi JLLatham, I've been using the latter methond you decribed in your code to refer to a worksheet i.e. Dim myWS As Worksheet Set myWS = Worksheets('Sheet3') It has worked for me up until recently. I've been trying to quite a large array (10000 rows, 37 columns)back to the worksheet but get the 'Excel VBA run-time error 1004: Application-defined or object-defined error' if the worksheet isn't selected; however it appears to run when the worksheet is selected. Do you have any ideas as to what could be going wrong/or how to fix this? Experts Exchange > Questions > Setting focus on textfield i Excel? 'The activate method in the OLEObject-class failed'. End(xlDown).Select myXLWrkSheet.Selection.End(xlToLeft).Select myXLWrkSheet.Range('A3:A4').Select myXLWrkSheet.Range('A4').Activate myXLWrkSheet.ActiveSheet.Paste Else myXLWrkSheet.Columns('A:V').Select myXLWrkSheet.Columns('A:V').EntireColumn.AutoFit myXLWrkSheet.rows('1:1'). The error message reads Select method of worksheet class failed. You will either need to make it visible before the select method is used or you will. Microsoft.Office.Interop.Excel OLEObjectClass class OLEObjectClass methods. OLEObjectClass methods Activate method. Activate method. First a bit of background: If you launch Microsoft Powerpoint, create an empty presentation, add an empty slide, insert an Object of type 'Microsoft Word Document'. You are now in edit mode in the OLE document ie Word. Type a bit of text, just enough to make a line without wrapping to the next line. Press the escape key. You're back in the powerpoint environment. Click on the object you have just created and resize it down in width using the mouse. What you can see is that the text is compressed. IE the shape is scaled rather than the object. Use undo to get it back to its previous size. Now right-click on the object and select Object->Edit. You're back in edit mode. Use the mouse once again to resize the object. What you should see is the text wrapping around to the next line as the control gets smaller. Keep it smaller and press escape. Now you're back in powerpoint with the control smaller and the text wrapped around. Ok, so all this to make a clear point. There are two sizes when dealing with an object.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2018
Categories |