Excel VBA Errors For Long Macro Processing?

Dec 30, 2013

I have a very long (> 2 hr) macro that sifts through thousands of files and creates a table of contents (more or less). Anyway, the macro will operate just fine when I am also working at the same PC but if I walk away for an hour, Excel will sometimes generate an error (application defined or object defined error). I believe that it has something to do with the PC going idle although it really isn't going idle because I turn off sleep and hibernate while running the macro. Also, there are only two locations where the error seems to occur. See below:

Code:
Dim oApp As ObjectSet oApp = CreateObject("Shell.Application")
Dim files As New Collection
Dim fZip As Variant
For Each fZip In oApp.Namespace(zipFile).items

View 7 Replies


ADVERTISEMENT

Formula To Calculate Result Whilst Avoiding Errors Is Too Long!

Jan 22, 2010

I have a formula I've been using for a long time which uses VLOOKUP to find results based on various reference cells, and then adds them up. To avoid errors caused by VLOOKUP not finding anything for one of the references I have also used ISERROR. The formula returns a blank if the calculation returns a zero.

I now need to bring further references in to the calculation but, using the format I have been, the formula is now too long. Here is my extensive formula:

View 3 Replies View Related

Excel Exits Macro Without Warning - No Errors

Jan 19, 2012

I'm writing a piece of code that puts certain values in certain cells based on certain criteria. (Vague, I know.)

When the code gets to the part where it puts the first value in the first cell (in a loop), it inserts the value and then the code stops running. Even when I put in "On Error Goto Err" with a msgbox that contains the error description, the code just quietly stops.

Here is a simple macro I wrote to see if maybe the problem had anything to do with something specific to my code. This one fails too, for no apparent reason. Could my file be corrupted or something? What do I do about this?

Code:
Sub rtest()

On Error GoTo err

Sheet2.Range("p4") = "test" 'code stops on first line, whether it is this one or the next every time.
ActiveCell.Value = "test"
ActiveCell.Value = "success"

End
err:
MsgBox err.Description

End Sub

View 9 Replies View Related

Speeding Up Macro Processing

Aug 27, 2009

Ive found a few ways to speed up the macro in my workbook thats taking about 40 minutes to process usually.

One way - Press Escape and it goes about twice as fast! - Wy does it do this?

Another way - Inserting these lines into the code -

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

View 9 Replies View Related

Macro Processing Time Is Very Slow (using Record Function)

Jun 17, 2009

I created this macro (below) using the record function but when I kick it off it takes 40 seconds or more to run.

View 4 Replies View Related

Split Up Huge Macro Using Call Macro But Pivot Table Code Errors Out?

Jul 10, 2012

I'm using a CALL Macro to split up a HUGE macro into different pieces:

Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual

[code]...

The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:

Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2

When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.

Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select

[code]...

View 4 Replies View Related

Excel Data (Date Errors)

May 29, 2013

I have downloaded data from a system software in our lab that gives dates and other data. The problem is that teh dates are in some sort of text format so even if I change the format to a date format it still doesnt recognise it as a date.

The date format is in a 2 digit year format which is why the error is being recognised. I have over 50,000 line items with this issue so changing each individually is not an option.

Is there any way of altering these dates so that it recognises the year as 2013 automatically.

View 4 Replies View Related

Excel 2002 :: Formula To Display All Errors?

May 27, 2012

In a worksheet the data (text or numerical) from five columns are compared using the following formula.

=IF($A10$G10,"1ERROR",IF($B10$H10,"2ERROR",IF($C10$I10,"3ERROR",IF($D10$J10,"4ERROR",IF($E10$K10,"5ERROR","-")))))

The formula is working well. The problem is I get the result for the first column of error even if the error is in more than one column. I want the result should display all the errors with the column number 1, 2, 3 (or with column name A, B, C etc). The total number of error is not required. The exact columns which have the error should be displayed. For example if the error exists in 3, 4 and 5 columns, I should get the result as "345Error" or "CDEError".I use excel 2002.

View 6 Replies View Related

Macro Fix Any Run Time Errors

Dec 29, 2008

I want to create a Macro that will basically fix any run time errors, that should occur without users having to close the document and reopen. I know I can hit "ctrl G" in the VBA and in the immediate window type in:

Application.EnableEvents = True

and then close the VBA and the macro will work again. I just want this code to be able to be applied by my users witout having to go into the VBA. I will assign this macro to a Button. How can I do this?

View 9 Replies View Related

Macro Progress Bar For Macro Taking Long Time

Dec 2, 2008

I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.

Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.

Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.

View 8 Replies View Related

Macro To Delete Cells With #N/A Errors

Aug 4, 2009

i have a column C with thousands of data. in that column, i had done a vlookup formula based on the lookup_value on its right, that is column B. some of the values arent available in the mapping table, causing the #N/A error.

i want to be able to delete those #N/A errors so that my dataset looks 'cleaner'. its tiring for me to go through each row one by one and delete them manually. i wnat to create a macro but im not so sure how to start.

in pseudo-code, it shud be something like this:

delete cell if cell_value = #N/A error.

View 9 Replies View Related

Random Errors When Macro Is Run In A Bat-file

Jun 6, 2007

I'm running loads of SQL-queries in a bat-file (using the isql-command). After that has been done I want to run Excel to format the result of the queries, and save it as a excel file on a networkplace. The SQL-queries are working fine, and the macro is working fine, when run seperatly, but since I cant predict how long the SQL-queries will take I have to either give it an extra long time before I start with the Macro, or run then in a chain from the bat-file.

When I do that I usually end up with random errors in Excel. Most usually it is "Not enough memory to display completly". This error is shown before the actual workbook is loaded, and therefore no macro has been loaded either so there's nothing I can do from there.

When I click OK the macro runs as should be though, but since it should be run at 5 in the morning it will just stand there until I come into work each day, and have loads of complaint that they havent got their figures yet.

I have checked out Microsoft, and googled the errors loads of times in diffrent way, but havent found anything solid that works yet.

View 6 Replies View Related

Identify Formula Errors Macro Code

Mar 6, 2008

i would like an if macro to pick up if cell dest (i have used a case to define this cell) contains an error or more imoprtantly #REF! then change the offending cells to 0 and put up a message box to put "Check XTA". i have found some that i think may work but i didnt understand them (they had function in them :smask so i couldnt put them in.is there a way to put them in with out functions or could someone point me in the right direction.

View 4 Replies View Related

Suppress Macro Code Run-Time Errors

May 9, 2008

how to supress the continue,end,debug, message when there is a vba error. The idea being that if there is a bug in my system that I have no realised, I don't want my end user seeing that message! I would preferrable design my own error message to appear instead.

View 2 Replies View Related

Excel Not Auto-completing Long Word?

Jun 24, 2013

I have to type in a long place name with 10 letters repeatedly and whereas before Excel completed it after I typed the first two letters it has now stopped doing so. Is there a way to reactivate this facility? Other names do get auto-completed when typed in the same column.

View 3 Replies View Related

Errors - Microsoft Excel Has Stopped Working - Crash In Pivot Table

Jun 10, 2013

I have created several reports containing pivot charts with slicers, pivot tables and data sets. Total file size is around 5MB. Need to do an update in my pivot table Like adding an additional grouping on one of my dimensions. As soon as I start doing so the Microsoft Excel stopped working error message pops up and my file crashes! Removing existing groupings seems for some reason to be impossible as well: nothing happens when I do this. Ahow to solve this without having to rebuild all my reports?

View 1 Replies View Related

Macro Has Errors If Visual Basic Editor Is Not Open

Nov 12, 2009

I've been helping another user create a workbook that dynamically adds, renames and deletes worksheets from a "Main Sheet".

I have got the whole thing figured out and running to satisfaction, except...

The macro runs fine if the Visual Basic Editor is open. If the editor is closed while the macro is run, I get "Runtime Error '9': Subscript out of range"

Any ideas what could be casing this? The errors occur when attempting to add sheets.

View 2 Replies View Related

Copy And Paste Macro Receiving Runtime Errors

Sep 4, 2007

I am getting a Runtime Error 13 Type Mismatch on some code that I am using. This macro was created to copy a range of data, prompt the user to open a specific workbook, prompt the user to select the uppermost left cell in the range, copy the data and then save and close.

Sub Copy_data9111() ...

View 3 Replies View Related

Excel File Takes Long Time To Save

Jan 20, 2014

[URL]

Excel takes about 10 minutes in the saving process. When I say 10 minutes, I mean, the excel screen freezes (says not responding) for about 10 minutes, then it actually saves at the very end in the normal time any other file would take as you watch the progress bar go forward.

I know many of the common answers and have tried. reducing the calculation time (which in turn reduces the saving time).

But in my circumstance, the calculation takes a very reasonable amount of time, and you see the progress % going forward.

- I would say I have about 2000 rows, and 15 columns.
- They have sumifs formulas.
- They link to a different workbook.
- The workbook I am working on saves to the network
- the source of my sumifs are also in the same folder on the network
- the recalculation takes about 10 seconds at most
- i have turned off recalculate before saving, it is all on manual calcs

- when i hit save, there are no calcs being performed
- there are no macros in the workbook
- there are only about 2 names in the name manager
- then it freezes for about 10 minutes.
- then the progress bar starts moving then it saves.

What is it doing in those 10 minutes?

1 more item to note, when I break the links to the workbook and thereby removing the sumifs formulas, its a snap.

Why does the existence of the sumifs extend saving time? I would completely understand if it elongated calculation time, but if calculation is off, then why does it even worry about it when saving?

View 4 Replies View Related

Error Handling Within Errors (macro To Create New Worksheets In The Workbook)

Aug 12, 2009

I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name.

As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error.

In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists.

My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed.

I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible?

My code so far:

Sub NewTrancheSheet()
'
Sheets("Tranche Sheet Template").Visible = True
Sheets("Tranche Sheet Template").Select

Sheets("Tranche Sheet Template").Copy Before:=Sheets(1)

Sheets("Tranche Sheet Template (2)").Select
Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count)
Sheets("Tranche Sheet Template (2)").Select.................

View 9 Replies View Related

Lightspeed Long Description Import From Excel - Separated Paragraphs

May 2, 2013

I am currently trying to import multiple 'long descriptions' onto Lightspeed product cards for display on an upcoming website. This is probably a very basic excel problem but I can't seem to find a simple solution online. Basically When I paste descriptions into a cell they break up into separate cells by line or paragraph break When I need them to stay together as one long description. Even if I have text qualifiers around the text they still only import one paragraph into lightspeed. Maybe that i am not saving it as the right file extension...

View 4 Replies View Related

Excel 2007 :: VBA - Finding Endless Loop In Long Routine?

May 14, 2014

I have a very long routine, looping through 35,000-ish rows several times to detect and delete unwanted items. Occasionally, it gets locked into an endless loop and CTRL+Break will not stop it - I have to kill Excel through the Task Manager.

How can I find out where the endless loop is so I can detect what's causingit and fix it?

View 6 Replies View Related

Extracting Multiple Numbers From Excel Cell Having Long Description?

Jun 12, 2014

I need to extract numbers from excel cells. For eg-

BAR TBG 04.00X02.25X26.50 1340 HRN SMLS SPEC. ES4.38694

^^^ This is one of the cells. I need to pull out 04.00, 2.25, 26.50, 1340 into different cells from one given cell.

I have tried using =MID() function, but I need to make changes into that formula everytime the position of first numeric letter changes while I go along a column. And I need to make changes into it as I go along the row.

It is taking me a lot of time in extracting numbers from one cell into different cells and I am afraid that I will spend the rest of my life getting this stuff done.

View 9 Replies View Related

Code To Time How Long Macro Takes To Run

Mar 10, 2004

Any code I can stick into the end (plus maybe start) of a macro to display the length of time it took for the macro to run?

View 9 Replies View Related

Worksheet Function Too Long To Be Recorded In Macro

May 11, 2009

I have a list of items in Sheet1 column A (starting from row 1).

I would like the corresponding column B to have certain values depending on the value in column A (same row), as follows:

If the value in column A has "SYS????CZ", column B should be "HPC"
If the value in column A has "SYSNIS", column B should be "NIS"
If the value in column A has "SYSJBE", column B should be "JBE"
If the value in column A has "ICG????", column B should be "HPC"
If the value in column A has "IL????", column B should be "RUP"
If the value in column A has "SYSHPC08", column B should be "HPC"

What I tried was to record a macro while typing the formula in B1: ...

View 9 Replies View Related

Convert Text To Long Number Macro

Aug 9, 2006

I have a range of numbers stored as text. I need to convert them to long numbers using vba. I tried typing a simple number = clng(a2), but that didnt work. Can someone help me with A) the right formula and B) the proper end(xlUp) format.

View 9 Replies View Related

Please Wait Message For Long Running Macro

Aug 21, 2006

When clicking the button on my userform, it goes through a quite complex process of changing the views in several pivot tables that are linked to an "report" sheet, which has all the figures the user needs in a neat format.

Changing these pivot tables takes up to a minute, so I wanted a userform, called "frmwait", to pop when clicking the command button. It would say "Generating Your Report - Please Wait". At the same time, the initial userform, which is called "frmroutedashboard", would be hidden.

This does not happen - the "frmwait" userform shows, however the "frmroutedashboard" does not hide.

Is this because the code I composed uses the values on the "frmroutedashboard" to generate the view, and it cannot hide until the report is completed? If so, there must be a way around this. Anyone know how?

The initial code so far looks like this:

frmroutedashboard.hide
Application.Cursor = xlWait
Application. ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."

I thought if it looked like this, it would be sufficient. Unfortunately, the file is too large to attach, so I hope the code suffices.

frmroutedashboard.hide
frmwait.show
Application.Cursor = xlWait
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.StatusBar = "Updating Route Dashboard..."

View 8 Replies View Related

Unable To Record Error, Long Formula Macro

Nov 25, 2008

I am working on using an excel workbook as part of a roll playing game and I am trying to record a macro and then put that in a command button which uses the randbetween, multiple if statements and multiple vlookup functions. I have successfully done similar things by just recording a macro and then pasting that into the VB editor. However this time I get a unable to record macro after putting the formula in the selected cell. I have tried copy and paste and just typing the formula with the same results.

Upon executing the formula I then want it to copy and paste special- values the result.

I'm assuming I have to put the VB code in manually but when I tried to do the formula I got a syntax error.

The worksheet I am working on is named new and I've included the formula in the a text box. I would like the result to show in J12 and be triggered by pressing the cmnbutton in k12.

View 9 Replies View Related

Hide Combobox Dropdown List During Long Macro

Mar 4, 2008

I've done some looking around in the object browser and in the forum, but i wasn't able to find the answer to this question:

I've got a combobox (dropdown list) in a userform which i use to run macros. soft of them are fairly long (2-3seconds), and during that time the dropdown list remains visible.... is there a way to force hide it?

View 6 Replies View Related

Processing Information

Oct 31, 2007

is there a vba code which I could use such that it will show me how much processing has be completed.

So basically the user first enters all the information required for the calculation(which is basically a bunch of records on which the processing is carried out), then they press a button which will start the calculation however currently the user has to keep on pressing the refresh button (ctrl+alt+F9) and keep on refressing until the results have been calculated then they are available to view.

I was thinking if it is possible that after the user click on the calculate button a form appears which will start the calculation and within the code it will automatically refresh the worksheet ('s) and then when a field within the worksheet (Status) changes to completed the form will then disappear.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved