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


ADVERTISEMENT

Spreadsheet Taking A Long Time To Open

May 28, 2009

I have a couple shreadsheets I have created, one is for Purchase order's and has 4 different sheets. with one sheet holding addresses linked a drop down menu in the first sheet, all in the same workbook. Also there is a macro within this sheet to print to a specific printer, and a specific number of copies when CTRL p is pressed. The total file size is approx 9 mb. Opening this file takes approx 30 to 45 seconds. There are no links to any outside files, only links within the workbook itself.

The second File I have recently started having problems with (Our Quotation log) is A workbook containing 3 sheets, 2 sheets independant and one sheet linked to the second that Summarizes the first and also has cels to enter information on the person the quotation has come from. I have this sheet formatted as a table so I can sort by name etc when doing followups.

This workbook also takes approx 30 to 45 seconds to open, and on occasion longer. All workbooks are stored on my computer. The PO workbook has had the problem since I created it, although the Quotation log workbook has only had the issue this week.

View 4 Replies View Related

VLOOKUP Performance Is Taking Very Long Time To Recalculate

Jan 11, 2010

I designed a spreadsheet which uses a lot of VLOOKUPs and it takes a very long time to recalculate. I don't know if there is another way I could do this, but this just seemed to make sense and it works just the way we want it but it just takes too long. Here is what the spreadsheet is designed to do:

We have data that is pasted into excel from another source. The data is broken up into account number, date, check number, and amount. Multiple payments from the same account can occur in one month so the data must be totaled. There is a custom function called concatif which works just like sum if but concatenates text. There are VLOOKUPs performed on the account number and the corresponding date, check number, and amount are inserted in the table.

View 3 Replies View Related

Formula Taking Long Time To Get Distinct Word?

Mar 15, 2012

I get distinct word with this formula i have 30,000 rows with below formula taking lot of time with dragging to all cell

{=INDEX(List,MATCH(0,COUNTIF($b$1:b1,List),0))}

View 5 Replies View Related

Large File Taking Long Time To Close - No Interaction From User

Apr 18, 2012

I have a large file that takes a long time to close.

I would like to display a message that says " SAVING - PLEASE WAIT".

But I do NOT want any interaction from the user to continue so I can not use a MSGBOX.

How can I do this?

View 7 Replies View Related

Sorting Macro :: Taking Time To Sort

Apr 11, 2007

I'm using the excel built in function to sort columns with my macro. However since my columns have 64,000 entries it takes a while for the computer to sort it.

View 14 Replies View Related

Macro For Progress Bar In Status Bar As A Percent Of Time

Jun 26, 2014

I have been looking for a macro (and have failed to find one) that would give a progress bar in status bar as a percent of the macro run time, e.g

If I had a macro that took say 5 minutes (which I would know in advance) to run is there a way to have a progress bar in status bar that would progress incrementally as a percentage of the 5 minutes or even simpler break the 5 minutes into 1 minute increments?

View 2 Replies View Related

Show Progress Indicator If Macro Execution Take Some Considerable Time

Jun 7, 2007

is it possible to show progress indicator if macro execution take some considerable time say more than 10 seconds?

View 2 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

For Loop Taking Too Long

Feb 26, 2009

I've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.

The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:


Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row

For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else

Cells(Rownum, 11).EntireRow.Delete shift:=xlUp

Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Column Taking Way Too Long To Delete Or Replace?

Jul 21, 2014

I have a data range with just under 100,000 lines by about 60 columns. In one of the final columns I used the formula =IF(COUNTIF($A$2:A2,A2)=1,1,0) in order to set a flag the first time that each unique value in Column A appears. (I know about the remove duplicates function but the data was sorted in a certain order and I needed information specifically from the first row in which each unique value appears.) The formula works - it's not very quick, but it works.

The issue I'm encountering though is that now I want to remove that formula so it doesn't keep recalculating and every technique I use either takes forever or stalls Excel entirely. I've seen the same issue in two different spreadsheets where I used the same technique. Techniques I've tried: selecting and deleting the entire column, copying and pasting values over the formula, Selecting all the cells and pressing delete, copying and pasting an empty cell over them, Selecting and cutting and pasting the cells to another workbook. I've done these while disabling automatic recalculation and even turning off Data Filtering. I just can't understand why a simple delete operation should take so long, it shouldn't matter what is in the cell because all I want to do is delete it.

View 3 Replies View Related

Progress Bar Macro

Jul 1, 2007

I'm using a mostly recorded macro to do some rather lengthy formatting in a workbook. The formatting is lengthy enough that I don't want people to think the PC is hung. So I'd like to use the progress bar. I'm trying to trim the code down to just the bare essentials for running the progress bar during the macro procedure (and so I can better understand what's happening in the code). The attachment shows what I've done so far. When I press the "Format Workbook" button then I have to press another button ("Run Progress Meter") to really launch the progress bar.

View 5 Replies View Related

Progress Bar While Macro Is Running?

Mar 10, 2014

I have a macro that takes about a minute to run. I would like to add a progress bar to appear once the macro starts running. There are no loops in the macro. What are my options?

View 3 Replies View Related

Display Progress Of Macro

Aug 28, 2007

I have a macro that runs when the file is opened and takes some time. I turn off the screen updating to speed the process. I want a msgbox, or something, to tell users that file is processing information that does not require a response to continue the code.

View 8 Replies View Related

Progress Bar For Macro Code

Apr 1, 2008

I am getting compile error for following code saying sub or function not defined with updateProgress getting highlighted

UserForm2.LabelProgress.Width = 0
UserForm2.Show vbModeless

MsgBox "get_blades (lookinname)"
UpdateProgress 0.25 ' 25%

Range("T3").Select
MsgBox "sort_data"
UpdateProgress 0.5 ' 50%

MsgBox "Get_Nom"
UpdateProgress 0.75 ' 75%
Range("a2").Select

MsgBox "get_info"
UpdateProgress 1 ' 100%

Worksheets("sheet2").Activate
Range("a1").Select

Unload UserForm2

View 2 Replies View Related

Progress Bar For Non-Looping Macro

Aug 12, 2008

I'm running a simulation that utilizes the Data Table function. The table itself is quite large (280 elements in the row, 10K in the column). It currently takes about an hour to run. (Full disclosure: I did this instead of a couple of "for" loops because I have to consolidate/manipulate a bunch of data from several sheets and it seemed to be running slower than a data table). how to write a progress bar for this type of code? I.e. code that does not loop and is just time-consuming?

View 4 Replies View Related

Taking Over Spreadsheet With Macro Not Working?

Jun 3, 2014

Debug error points to:

BuildPlan(Worksheets(SourceWorksheet).Cells(j, "A"), Worksheets(SourceWorksheet).Cells(j, "E"),
Worksheets(SourceWorksheet).Cells(j, "F")) = Worksheets(SourceWorksheet).Cells(j, "K")

Sub is below:

Sub UpdateMonthForecast()
Dim Month As Integer
Dim Year As Integer

[Code]....

View 2 Replies View Related

Taking Data From Other Spreadsheets Using A Macro

Oct 3, 2007

I have a number of reports in excel (all in the same format) which I need to take certain data out of and store it into one main spreadsheet. Searching through the forums I found some code which I think will do this:

Sub test()
Dim myDir As String, fn As String, ws As Worksheet
myDir = "C: est"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
Set ws = Workbooks.Open(myDir & fn).Sheets(1)
ws.Range("a7", ws.Range("a" & Rows.Count).End(xlUp)).EntireRow.Copy
ThisWorkbook.Sheets(1).Range("a" & Rows.Count).End(xlUp).Offset(1)
Workbooks(fn).Close False
fn = Dir
Loop
End Sub
I have tried using this code, altering the Dir String to the folder the documents are in.

However, when I run the code, nothing happens. I don't get any errors, and nothing appears in the spread sheet.

To be honest, I am not totally familiar with macros in Excel. I can get the gist of what the code is doing having using VB before, but am not totally familiar with some of the functions being used here.

View 9 Replies View Related

Progress Bar To Work While Running The Macro

Jul 6, 2005

I was looking at a previous post to get a progress bar to work while running
a lengthy macro. Below is the post on how to incorporate the progress bar
into your code.

That works fine expect for one thing. There is a cancel button on the progress bar, and when it is pushed the rest of my code continues running. How do I end my code if I press cancel on the progress bar?

Sub Main
Dim PB as clsProgBar
Set PB = new clsProgBar

'if your subs are being called from a userform, hide the form first using
Me.Hide
With PB

.title = "some title"
.caption1 = "Executing, this may take a while"
.caption2 = "Doing task 1"
.show
doevents...................................................

View 14 Replies View Related

Macro Progress Bar To Show % Process Done

Oct 25, 2008

As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....

I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?

Or even tell me which sub routine it is up to? (but % would probably be better)

And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...

View 14 Replies View Related

Diplay Progress Of Slow Macro

Jan 14, 2008

I'm running some macros that take a bit of time (1000-2000ms), and would like to give the user some feedback as to what's happening.... Ideally, I'd like to show a msgbox-like pop-up window saying something "processing..." while the macro executes.

I have tried using userforms (based on this post Excel VBA Macro- How to Create a Splash Screen for your Excel Workbook); however they halt background execution...

Is there any built-in functionality similar to .statusbar, but more obvious/visual that would allow this? I'm about to experiment with showing/hiding a textbox shape, but this seems a bit sloppy...

View 2 Replies View Related

Display Progress Of Macro Procedure & Interrupt Code

Feb 26, 2008

I have a program that screen scrapes data fom a mainframe emulation program. There are many loops used in order to slow the program down checking for things to load before taking the information. I would like to build some kind of display showing the user what the program is doing and allow them to stop the program if they like. Right now while the program is running everything is frozen and unresponsive.

View 3 Replies View Related

Taking Text In A Long Column And Converting It Into Corresponding Text In Another?

Sep 27, 2013

I have two columns that read:

XXHSXX HR
XXTRXX MG
XXXFXX XG
XXHSXX HS
XXTRXX MG
XXMEXX RU
XXHSXX HR
XXHSXX
XXTRXX
XXTRXX
....

The above list represents 2 columns. The left column is 400 lines long. The right column is partially complete with 2 letter codes that represent the 6 letter codes on the right. I'm trying to convert the column in the XXYZXX format to a 2 letter column and each of those 2 letters corresponds to the 6 letter column on the left. How do I do this using any method or Vlookup? How does one convert a column of text to a corresponding column of text? I don't want to have to manually type in 2 letter codes that match up the left column. What do I do instead?

View 2 Replies View Related

Taking End Text From Long String Of Text And Dot

Jun 14, 2013

I have a bunch of these following below which I need extract text from the first "." from the right hand side.

The amount of text and "." can vary as does the number of characters.

XXXXXXXX.XXXXXX.XXXX.XXX.XXXXX.XXXXXXXXXXXXX.XXXXXXXXXX.Vibrating Screen SC409
XXXXXXXX.XXXXXX.XXXXX.XXXXX.XXXXXXXX.XXXXXXXXXX.Scalping Circuit 4
XXXXXXXX.XXXXXX.XXXXXXX.XXX.XXXX.XXXXX.Fines Stacker ST601

Ultimately i want it to display the following:

Vibrating Screen SC409
Scalping Circuit 4
Fines Stacker ST601

I have used LEFT, RIGHT, SEARCH, LEN functions in various combinations but cant get it to reference the last "." from the right.

View 3 Replies View Related

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 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







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