A large Excel file that I have been working with for months is now giving me trouble.
When I tried to delete 2 rows, it displayed a message: "Excel cannot complete this task with avail resources. Choose less data or close other applications."
I opened a previously saved version of the file and tried deleting rows, and the same message came up.
I shut down the computer, rebooted, same problem.
Any way to determine if a file has been corrupted .... or even worse ... is there a possibility that my Excel program itself is corrupted?
How do you generate an error message that states "You are not authorized to delete this worksheet" any time a user attempts to delete the specified worksheet? This message also has to block the user for deleting the worksheet.
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).
Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.
Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to
whether I can handle errors in a way that tells me which object caused the error?
For example if a userform has two combobox's and one causes an error, can I return to a message box containing the name of the object (combobox1 or combobox2)?
I have tried returning err.source but I am either not using it correctly or it isn't intended to be used this way.
I'm not the sharpest tool in the shed when it comes to excel programming but I manage. However, I can't seem to get rid of this run-time error 13. Could anyone take a quick peek at the document and spot the problem?
My intention is this, when typing 'Yes' in the AB column on a certain row, that row is cut from that worksheet and moved to the 'Archive' worksheet, also, simultaneously, I want to send a mail to a specific address.
Here is when I run into the error, I suspect when the row is removed with the 'Yes', those two macros clash.
I have a form on a SheetA wherein all cells are referencing the data in cells on SheetB. When I delete or add a row on SheetB, the cells in that row on SheetA read REF#. I need the row in SheetA to be added when I add it in SheetB and vice versa for when I delete a row.
I have this code that I have had for a while.. it works okay on my computer and does as intended... it deleted all empty rows in the selected range after checking to see if any cells contain anything that makes it look blank but isn't (it cleans those cells).
So on my computer it works... on a coworkers computer it converts every used cell in the usedrange to #VALUE...
I'm trying to use the code below to copy active sheet into a new workbook and clean some parts of it so it will be ready for next department's data input work. But when it comes to deleting empty rows in it, code gives Run-time error "1004" : Delete method of Range class failed error and it marks the part "Rows(r).Delete"
I want to establish a link from my worksheets. Using the conventional link method I was able to link the values from my first worksheet to the second worksheet. My problem is when I delete a particular row. The reference of the second worksheet will have an error #REF! since I deleted those cells. Is there any way that I can link my two worksheets without any error that even if I deleted a particular cell/row the reference is still intact?
why the following code generates an error if I use the backspace key to clear the textbox?
Code: Dim t As Date If Len(Controls.Item("txtstart").Value) = 4 Then On Error Resume Next t = TimeSerial(Left(Controls.Item("txtstart").Value, 2), Right(Controls.Item("txtstart").Value, 2), 0) On Error GoTo 0 ElseIf Len(Controls.Item("txtstart").Value) = 5 Then On Error Resume Next
The code below works nicely to insert a time stamp in column B when the user selects "Yes" in the corresponding cell in column A. The problem is if I try to delete any rows or clear the contents of the cells I receive a "Run-time Error 13 - Type mismatch." If I delete the contents of the cells one at a time I do not receive the error. What I am trying to do is the user has a list of items to select from in column A. Only if "Yes" is selected I want the time stamp in column B to appear.
Code is below.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target If .Value "Yes" Then Exit Sub
If Not Intersect(Range("A1:A50"), .Cells) Is Nothing Then Application.EnableEvents = False With .Offset(0, 1)
why my code would hit a run time error on a shared file? It works fine unshared. This is on a ActivateSheet Worksheet Event, Code is housed in a Module and called when activated.
why this is causing an error upon opening the file. This workbook has a userform with a listbox (single list, single selection), and when I have the ControlSource property set it causes an error upon opening the userform (which opens when the file is opened), whereas if I do not have the ControlSource property set to anything, it seems to work fine.
Interestingly, if I set the file up to not open the userform with the file, and instead have the userform open when a worksheet control such as a command button is clicked, it doesn't seem to matter if the ControlSource property is set or not. The workbook in question is attached.
I am pulling external data from an Access 2007 database query that uses the nz function. While linking to the query, I get an "Undefined Function nz" error. The query works perfect when run in Access.
-2147417848 automation error the object invoked has disconnected from its clients
Unfortunately, I can't tell where the error is occurring because everytime it happens excel crashes or at least stops responding. I tried waiting it out (about 20 minutes) but no luck. the only thing I can do is force the program to shut down through the task manager.
The program is designed to pull information from finviz.com (a financial site), filter the data. pull the next round of data and run the filters again. there are a total of 8 queries and i think it gets hung up during the 6th or 7th run through.
Stepping through the code does not seem practical since each query might pull as many as 300 rows of data that is evaluated in a for/next loop (thats a lot of F8s).
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
I have just signed up to the forums as I am having a very specific problem. I have looked on google and used the search feature (on several forums!) and found very limited information that is useful to me. I am having trouble with a very simple few lines of code in a macro. The code looks like this:
Function startmarketIDs() Dim targeturl, writerow, readrow, textmass, xmlHTTP targeturl = "http://lite.betfair.com/ Events.do?s=00010913z" Set xmlHTTP = CreateObject("Microsoft.xmlHTTP") xmlHTTP.Open "GET", targeturl, False xmlHTTP.send MsgBox xmlHTTP.StatusText textmass = xmlHTTP.responsetext MsgBox textmass End Function
My problem occurs at the line "textmass = xmlHTTP.responsetext". The responsetext command is obviously causing problems because if I remove it, the code executes without error and the "xmlHTTP.statustext" says "OK". With the responsetext command left in, the code generates the following error: Run-time error '-1072896658 (c00ce56e)': System Error: -1072896658. If I change the targeturl to www.betfair.com, the code executes fine and I get a message box (as desired) with the source code of the website displayed. Without meaning to sound like Im answering my own question (!!), perhaps there is something about the url or the way in which I am using the xmlHTTP object that is causing the issue, I wondered if someone would mind inspecting my code and perhaps pointing me in the right direction?
I have been browsing the web trying to find a worksheet event that will automatically open a message box if the user tries to delete the sheet. Is this possible??
PS I do not want them to delete the sheet, so perhaps a message box with a a cancel button
I am looking for a more efficient way to write a macro (a sample from the macro is below). This is just the first part of the macro. I need to repeat these same steps (seen for row 5 below) for rows 5 to 50. My script worked until I hit row 35 and then I got the "compile error.." message. There must be a way to use "loop" to write this more efficiently, no?
For some reason my form won't open when the workbook is opened. I get an error message "run time error '424' object required" (which happens when I have Form1.show in the BOTH workbook_open event and the userform_initialize event (oops)). When I removed form1.show from the userform_initialize I don't get an error but I also get no form. I recall having this issue before but I can't recall how to fix it.
Can I call the userform_initialize event from the workbook open event to get around this successfully and properly?
I do hit the SCE (Shift-CTRL-Enter) keys. The formula does work in one workbook, but not the other. The only difference between the two workbooks is this formula has a larger range.
New Formula- doesn't work (ERROR- #DIV/0!) {=AVERAGE(IF(Sheet1!A3:A1094="MONDAY",Sheet1!E3:E1094))}
Old Formula- works {=AVERAGE(IF(B6:B451="MONDAY",E6:E451))}
I get spreadsheets sent to me with hours and minutes in decimal format. Frequently a quarter of an hour or three quarters of an hour are posted as 0.15 or 0.45 instead of 0.25 and 0.75. Other odd fractions can also appear.
I want an error message if the value when divisible by 0.25 is not a whole number.
