i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
http://ifile.it/6v2j39f
(You start the macro using "run" command in "input" sheet)
So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p
I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..
And here's some reference for Floyd's algorithm,
http://ifile.it/12p5zle
I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.
Did not want to hijack Jonny's thread about ways in which to update and streamline code to make it run faster. The update speed of my first attempt at a macro is woefully slow - 5 minutes for 2.5K records. I was wondering if some of you more knowledgeable folk could look at my code and make suggestions as to how it could be modified to run faster.
When I ctrl-break out of it I usually end up in a private sub that concatenates columns A & B in Column C (see below). When it resorts the database it must keep triggering this concatenate sub which (I think) is slowing things down.
Concatenate Private Sub:
Option Explicit
Private Sub CommandButton1_Click()
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub ......................
This may be a long shot, but I've been working on a macro today which runs a few Vlookups, does some copying and pasting and some re-aligning....basically nothing too complicated. The Vlookup is looking through approx 45,000 records and returning about 200 - 300 on average, this info returned is company name, address, contact info etc etc.
This macro is taking a ludicrously long time to run, I've checked through and removed any coding that I think is unnecessary without altering the final outcome of the macro.
It still takes ages. Has anyone got any ideas why this may be, or any tips of speeding it up?
I have a brand new computer, dual core processor, 2 gigs of ram etc so it shouldn't be performance of the machine slowing it down, and I ran it with the least applications open that I can (I'm at work so can't close everything, but none of the other applications are memory hogs or anything). I'm running on Office 2003 on Windows XP.
I am running a rather large simmulation where I have 12 for loops with each running between 0 and 100% with variable steps and the where the sum of the 12 variables has to sum to 100% and sometimes combinations of the sums of a few of the 12 have to sum up to specific values. That part works, now I am trying to get this to run faster.
Question: Is there a different design that might be able to run this process more efficiently? If not, is there a way to speed up the process by letting the computer know that all these variables are between 0 and 1 (or 100%) and that I only need 3 digits after the decimal if the value is in % form?
(the only problem with the rounding or cutting off the decimals at the end that I can think of is that it will only speed up the process a bit but will also make the summing upto 100% or any other value difficult)
I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.
This code is pretty slow if most rows have to be hidden. I'm certain there is a faster way to do this but I'm not seeing it.
What am I doing with For each row from 12 to 236 I'm looking to see if there is data in any cell in columns A:F, H, & J. If not, I hide the row. I'm starting at row 236 and working up to 12 continuing to hide rows until I find data or I reach row 11. As soon as any row has data or row 11 is reached the the procedure ends leaving all remaining rows visible ...
However, this runs extremely slow. I had to stop it after about 10 min, as it only got to about row 1000 of 20,000.
Sub Delete_Dupes() Dim rw1 As Long: rw1 = 1 Dim rwx As Long: rwx = rw1 Dim stepx As Integer Dim co1 As Integer: co1 = 1 Dim co2 As Integer: co2 = 2 Dim bool1 As Boolean Dim bool2 As Boolean Dim bool As Boolean Dim count As Integer count = 0 Do Until Cells(rwx, co1) = "" stepx = 1 If rwx > rw1 Then On Error GoTo NewCrit bool1 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co1), Range(Cells(rw1, co1), Cells(rwx - 1, co1)), 0)) bool2 = IsError(Application.WorksheetFunction.Match(Cells(rwx, co2), Range(Cells(rw1, co2), Cells(rwx - 1, co2)), 0)).................
1. I need to paste data that is copied from an internet window into sheet1 without selecting the cell so that I can continue working on a data in sheet5. The macro prevents me from editing sheet5 in an orderly fashion because when it goes to paste the data into sheet1 it selects the cell in sheet1 interupting what I am trying to manually do with the data in sheet5. How to paste data into a cell withou selecting the cell so that my program stops interupting what I am doing in a different sheet?
2. My macro is meant for real-time defect monitoring in a production facility. The macro opens an IE window that contains the defect occurences, copies the data, pastes it in sheet 1 then sheets2 and 3 analyze the data, closes the IE window, and then waits for a time period before repeating the process. This macro is so slow though that it is hard to work on this file while the macro is running. How to run the macro in the background and speed it up so that I can continue working on Sheet5 of the file while the macro repeats itself over and over again? Here is my code.
The variable website is determined by earlier parts of the code that direct the macro to a specific network website. Sub Half_Hour_Data
1 'Open Internet Window and Navigate to Website Set myIE = CreateObject("InternetExplorer.Application") myIE.Navigate Website myIE.Visible = True Application.Wait Now + TimeSerial(0, 0, 10) needed to wait until the page loads
[code]....
Is there a way to speed up the macro by removing the timeserials and just telling it to continue when the task is complete. Then run the whole thing in the background so I can work on another sheet while this program runs?
I need to know if it's possible to log the process of a series of macros either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)
For example:
Sub MyMacro1 () code End Sub
Sub MyMacro2 () code End Sub
If this process was logged, then the log file would look something like:
Date Time : Sub MyMacro1 Date Time : End Sub Date Time : Sub MyMacro2 Date Time : End Sub
But it would be a in text just like a log file. Is this possible with Excel ?
The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.
I wanted to know If there a way to hide the process of executing macrob (vba)? I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.
I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?
I am trying to speed up this macro, ive already tried turning screenupdating and calculation off, but it still takes forever, and I dont understand why.
The code is basically searching for a given string in column E. There are about 9000 lines to my sheet, which shouldnt take more than a 30 seconds to a minute to complete.
Private Sub CommandButton1_Click() Application.Calculation = xlCalculationManual
For MY_ROWS = Range("E12000").End(xlUp).Row To 1 Step -1 If Range("E" & MY_ROWS).Value UserForm1.ComboBox1.Value Then Range("A" & MY_ROWS & ":E" & MY_ROWS).Delete (xlUp) End If Next MY_ROWS
Also anyone know how to add another condition to make the macro stop the delete process at lets say Range("a1:e8")?
I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.
Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.
Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.
I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.
Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.
Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.
I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.
so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?
I am trying to write a macro that will run another macro (already written) through all the sheets/tabs in a workbook one sheet at a time and will stop once it reaches the last sheet/ tab.
I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??
Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.
The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!
How can I modify this code so I can select more than one file? I need something like for each wb .....at the end next wb...until the macro has done the below for all selected workbooks.
VB: Sub openandcopy()
Dim wb As Workbook Dim ws As Worksheet Set ws = ActiveSheet Set wb = Workbooks.Open(Application.GetOpenFilename)
I need to get this macro to process the cells for every worksheet in a book rather than just the active one
Public Sub test() Dim Lr As Long, i As Long, x As Range, _ v1 As String, v2 As String, v3 As String Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious) If x Is Nothing Then Exit Sub Lr = x.Row Application.ScreenUpdating = False For i = Lr To 1 Step -1 v1 = Cells(i, 2) v2 = Mid(Cells(i, 3), 1, 1) v3 = Cells(i, 4) If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete Next Application.ScreenUpdating = True End Sub
My problem is that data from previous applications are "bleeding" into new applications while running my macro.
I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.
I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.
I am using Excel 2003, SP2
Sub Process_Application() ' ' Process_Application Macro ' Macro recorded 3/23/2009 '
I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.
A brief description of the macro.
The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.
Sub files() Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer Dim filetype As String Application.ScreenUpdating = False
my worry now is to protect my program from being played with as a lot of people ,some not so bright ,have access to my sheet can i protect my sheet and still have the macros function.
Sometimes I need to copy something to the clipboard, then go to another sheet (or somewhere). When I get "there", I sometimes want to insert rows, without "inserting cut/copied cells" . . . . So I end up saving the worksheet to clear the clipboard, then inserting my rows.
Can I insert blank rows while maintaining my clipboard?
I have a Weight Watcher spreadsheet. I enter the Calories, Fat and Fiber and I get the # of points for that food. Then I keep put those points into the lower part of the spreadsheet where I track what I eat, and Excel does my simple math for me. What I want to do is keep a list of my foods, and their point value in my spreadsheet. I would like to be able to put the food name in Cell A1, the WW points in BI, and then be able to click a button with a macro that takes those two pieces of data and add them to bottom of my food list. I don't have a food list yet, I would assume I can just put it on sheet 2. I then want to be able to use lists in my cells to choose the food in the future. I need to be reminded how to make "dynamic named ranges. Today, I just need to know how to write the macro to copy and paste those two cells in the first open row at the bottom of my food list.
When I view my page on print preview it shows 2 pages but I only have stuff on one page the second page shows 2 border marks but when I go to erase them I can't find them. Is there some way that I can find them other than look at page after blank page.
