Finding Sets Code Slow To Run
Aug 23, 2006
If you open the file sets.xls, you will see that I have listed 5 sets of 10 numbers.
A21:J21
A22:J22
A23:J23
A24:J24
A25:J25
These sets are such that all 10 numbers matches with sets of 20 numbers listed vertically in the range K1:S20 atleast twice (T21:T25 -twice)
I know there are 50+ sets like these and I have listed just 5 of them.
How can I search the other sets?
I have uploaded a zip file which has an excel file. Run the module "DoIt" and you will get all 50+ sets like these.
The problem is that the module takes 42 minutes on my Celeron 800 Mhz 256 MB SDRAM. I want to reduce this time.
View 4 Replies
ADVERTISEMENT
Jun 13, 2013
I have a code in software that exports results to excel. But it stores the numbers associated with indices as text. I need to change all of them to numbers. So I have written the following code, that search through the folder, converts text to numbers and colses the file.
Sub ConvertText2NumberFiles()
Dim v As Variant
Dim rng1 As Range, bk As Workbook
Dim i As Long
ChDrive "C"
ChDir "C: ...analysis"
[Code]....
The problem is it takes time becuase it is going through A LOT OF cells. I only need at most the first 4 columns and the first 2 rows. So I wanted to use the union. I wrote the following:
Set IndexColumns = bk.ActiveSheet.Range(Columns("A") & ":" & Columns("A").SpecialCells(xlLastCell))
Set IndexRows = bk.ActiveSheet.Range(Rows("1") & ":" & Rows("1").SpecialCells(xlLastCell))
Application.Union(Range("IndexColumns"), Range("IndexRows")).Select
Which doesn't work! Morover it is only for one column and one row.
NOTE1 : Like any matrix, the intersection of index columns and index rows is blank!
NOTE2 : The lenght of indices is different from one file to another
View 2 Replies
View Related
Nov 6, 2008
My code runs so slow! The sheet only has 233 rows and 6 of them are not in the loop.
View 6 Replies
View Related
Mar 27, 2009
Very slow small vba code. I have the following
View 2 Replies
View Related
May 9, 2007
I have the following piece of
Sub Summarise_Variations()
Dim myRange As Range
Dim Write_Cell As Long
With Application
.Calculation = xlCalculationManual
.StatusBar = "Please wait - Summarising variations..."
End With
It happily goes through a list and sums multiple records. It might not be the most efficient or best way to do it but it works and when I run it on a couple of stand alone sheets it takes a couple of seconds to process if that.
Unfortunately when I run it as part of the overall application that I've developed it takes ages to run, i.e. more like ten minutes.
The spreadsheet has a few graphs and about 250 sumproduct and array formula live in it but all other formula on other sheets are created and then paste valued as part of other VBA routines. As you can see I've also turned calculation off as the procedure runs so don't understand why it is suddenly taking so long.
View 9 Replies
View Related
Aug 4, 2008
way the code below can be made more efficient? Basically, I drop down a combobox, and based on the value i select, it populates a lot of textboxes etc based on the listindex.....
Private Sub ComboBox3_Click()
On Error Resume Next
With Me.ComboBox3
If .ListIndex = -1 Then Exit Sub
Label23.Caption = Sheets("overview").Cells(.ListIndex + 2, "c").Value
Label24.Caption = Sheets("overview").Cells(.ListIndex + 2, "b").Value
Label38.Caption = Sheets("overview").Cells(.ListIndex + 2, "d").Value
TextBox1.Text = Sheets("overview").Cells(.ListIndex + 2, "e").Value
TextBox2.Text = Sheets("overview").Cells(.ListIndex + 2, "f").Value
TextBox3.Text = Sheets("overview").Cells(.ListIndex + 2, "g").Value
It takes about 10 seconds every time i choose another project
View 9 Replies
View Related
Mar 8, 2007
I do those things, I set every object = nothing... I even moved them up to right after I use the object. When I open the workbook, my process's shows excel at 38000K. my first go of running code, stepping thru is great... I watch my process and see that I get up to 78000K... I then exit my code, properly... and the process goes only down to about 50000K. If I run it a 2nd time excel process show almost a 100000k and stepping thru is slow, slow motion... a 3rd time and I am at 120000k. I am setting everything to nothing and can't figure it out... I have done bigger projects with more forms, recordsets, collections and never have had this problem.
View 2 Replies
View Related
Sep 15, 2009
I have some code which looks for the word "Out" in column "L" and hides the row if it finds it. This is running from row 24 to 160 and takes about a minute to run. I've tried two different solutions, neither really quicker than the other.
View 3 Replies
View Related
May 3, 2009
I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
View 9 Replies
View Related
Aug 18, 2009
I have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25
Sub IC_Delays()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Add
Windows("Delays List.xls").Activate
Range("F15:N" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Windows("Book4").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select.........
View 9 Replies
View Related
Apr 4, 2009
The code opens a varying workbook with the same format as the source workbook and copies specific columns into the source workbook.
It is currently taking about 20seconds to run.
The worksheets are protected and have merged cells hence the call TestMe line.
View 13 Replies
View Related
Oct 1, 2006
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click()
Dim Val
Val = "6"
Dim valand As Range
Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0)
valand = Val
Dim A As Range, B, C, d, E, F, G, H, I, J
Set A = ThisWorkbook.Worksheets("Stageing").Range("K2")
Set B = A.Offset(1, 0)
Set C = B.Offset(1, 0)
Set d = C.Offset(1, 0)
Set E = d.Offset(1, 0)
Set F = E.Offset(1, 0)
Set G = F.Offset(1, 0)
Set H = G.Offset(1, 0)
Set I = H.Offset(1, 0)
Set J = I.Offset(1, 0)
TextBox1.Value = A & B & C & d & E & F & G & H & I
End Sub
View 4 Replies
View Related
Jan 23, 2008
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade()
Dim icolor, Navn As Integer
Dim TargetRow, LastRow, Previous As Long
Dim Target As Range
MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen"
Range("A5").Activate
Range("A5"). CurrentRegion.Select
'Set range
ActiveCell.Offset(Selection.Rows.Count, 0).Activate
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
View 8 Replies
View Related
Mar 5, 2013
I have an excel spreadsheet that performs a particular calculations using a large set of data. However, I have over 1000 sets of data that need to be feed into this sheet and obtain the output calculation. Obviously I don't want to have to copy and paste in each new set of data to obtain my result as this would take a very long time. I am brand new to VBA, so was wondering if there is a way to write code in VBA that will automatically perform these calculations for one data set, save the output into a cell, and then move to the next data set and so on?
View 4 Replies
View Related
Jul 13, 2013
I am working on large sets of data (more than 50,000 rows of data). I have two sets of data. Set 1 and Set 2 (master data) on the same worksheet. Both the sets of data have three columns each. I am using EXCEL 2007. I was able to accomplish step 1 below.. but I am totally lost with step 2 since i have an additional criteria for the "year".
I have attached the excel sheet as well. This is what I am trying to accomplish:
1) I want to find exact matches in set 1 and set 2 and highlight it or do something to show that a match was found. The challenge is the data in set 1 can occur anywhere in set 2.
2) Add to the complication .. my criteria for matching the year is different. If the Set 1 "year" is equal to or greater by 1 yr or greater by 2 yr when compared to Set 2 "year", I want to treat it as a "match".
For example, from the data attached:
Set 1 data in row 4 is: ATLANTIC ABSECON 2004
Set 2 data in row 3 is: ATLANTIC ABSECON 2003
I want to treat these two data as "MATCH" since ATLANTIC matches ATLANTIC, ABSECON matches ABSECON and according to one of my criteria for year, Set 1 "year" is greater by 1 yr than the Set 2 "year".
View 1 Replies
View Related
Oct 7, 2013
this code to minimize it and avoid copy to clipboard.
VB:
Sub SapOutputRun()
Application.EnableEvents = False
Application.ScreenUpdating = False
[Code].....
View 2 Replies
View Related
Feb 19, 2012
I am using the below to open a text file and copy the used range to my working workbook. The opening of the file makes the code slow and and also it may be bcoz i am trying to paste 11800 lines of data from one file to another.
Also i can see the flickering of the screen when the files are open. How to make the code faster and more efficient.
Code:
Sub readSimFile()
Application.ScreenUpdating = False
'//code//
Application.Calculation = xlCalculationManual
[Code] ......
View 5 Replies
View Related
Apr 10, 2008
Below is a series of sets. Column A is the set number. I need a macro that will insert a row between sets and then put a border around each set. In my spreadsheet the sets are from A1:C500. Sometimes the sets are only 1 row, sometimes multiple rows. It looks like I will be doing one of these sheets every week. So far I have been doing it manually, but a macro sure would save some time.
View 13 Replies
View Related
Mar 25, 2008
my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.
Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub
Sub UpdateEntries()......................
View 7 Replies
View Related
Jul 2, 2014
What would be the code to look for the last row with data in it, the going through it cell for cell and setting 0 in all the empty cells ?
View 12 Replies
View Related
Jul 15, 2009
I am really new to VBA programming and I have to automate an excel sheet which has the columns Closed Qtr, Apps Bucket and Res Time.
Closed Qtr has the following entries: Q107,Q207,Q307,Q407,Q108,Q208,Q308,Q408.
Apps Bucket has the following entries:
BU Apps,Global Apps,Quality,others
I have to find the median of the Res Time data based on grouping by Closed Qtr and Apps Bucket, i.e., for BU Apps in Q107, BU Apps in Q207 and so on.
View 6 Replies
View Related
Apr 25, 2014
I am trying to find an item within an array and then VBA could execute code.
Here is an example:
[Code]....
LookupItems =("text", "value", "book") or should I acutally be using Split("text,value,book",",")
For Each sht in ThisWorkbook
If sht.name = array(LookupItems) Then.....execute code
[Code] ....
So basically in this example I want to loop through all the sheet names in the workbook and if any of the names in the arrary are found it will execute the code for those particular sheet names.
I know alternatives are the select case or write an if statement for each value I am looking up or even use an OR for each value to lookup; but I just wanted to see if this method was even possible as it would be less coding.
View 2 Replies
View Related
Apr 24, 2009
I have a really nice blue colour that I want to incorporate into all my spreadsheets, but I cannot suss out what the RGB code is, is there some code that could do the trick?
View 2 Replies
View Related
Jan 20, 2013
Need a code that sends emails to the intended receipients based on hold codes if Columnd J reads as YES Email formats are listed on "Email Format" tab, code should pick the required field value from the table (Hold Report).
Analyst name, Phone number are entered in the text boxes (data validation for these is desired)
option boxes are used to send email based on the selection as below
if "send initial email without attachment" is selected then emails should be sent without the attachments
if "send initial email with attachment" is selected then emails should be sent with the attachments
Attachments are usually .PDF files and are stored on users desktop with file name as .pdf
if the follow up otion is selected the subject line on the mail should be appended as *** 1st Follow-Up*** along with standard subject line
Rest of the requirement remains same for followup option (same email format is used) based on the option selected, code should be able to send emails with or without attachments. I have provided three command buttons that are intended to work as follows;
Validate data: need to validate the data to ensure that each row has atleast one email address in "TO" column and there are no spaces in the email address.
Clear Data: Should clear the existing data from the table
Send Emails: Should send emails
Standard subject line is a combination as below
Invoice on Hold for ; Invoice number: ; PO Number: ; Invoice Amount:
Note: Column H and I apply only for hold codes Qty Ord and Max Ship Amount
I use Microsoft 2007.
View 3 Replies
View Related
Mar 30, 2009
I have a workbook made up of worksheets containing source data, pivot tables and charts but it is now becoming very slow since I have added a worksheet with many formulas.
If I move that worksheet containing many formulas (20 columns by 150 rows) to a different workbook will that help speed both of them up? The formulas will obviously still by referring to the original workbook.
Or is it simply by using formulas it slows things down whatever you do and I will have to think of a clever way of using pivot tables instead? Are there any other things that could be slowing it down? Other workbooks I maintain are fine.
View 9 Replies
View Related
Dec 5, 2009
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.
Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my
View 14 Replies
View Related
Nov 14, 2006
My spreadsheet is chugging through the following code. The data being filtered is rows A:AC, rows 10:193 (so not an absurdly large amount of data). It takes about 15 seconds to complete the filter. Ideas?
Private Sub EuropeButton_Click()
HeaderRegion = "Europe"
ActiveSheet.AutoFilterMode = False
Range("A2:A8").Value = 0
Range("A2").Value = "TEG"
Range("A3").Value = "TUK"
Range("A4").Value = "TEF"
Range("A10").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("A1:A4"), Unique:=False
If Columns("E:F").Hidden = True Then
Columns("E:F").Hidden = False
End If
End Sub
View 9 Replies
View Related
Mar 14, 2007
I'm rather new at VBA
I have a small workbook with a userform and some buttons. Each time I press a button a counter increases and the workbook is saved.
This saving seems much too long for such a small file.
I use
Activeworkbook. save
View 9 Replies
View Related
Mar 19, 2007
I'm working working with this spreadsheet that is moving incredibly slow. Every time I enter anything, it takes anywhere from 10 seconds, to a couple minutes to calculate and let me proceed. It is a pretty big file (4.60 MB), but I also work with another spreadsheet that is a little smaller (2.95 MB) that has never taken more than a fraction of a second to calculate anything. What could I do to spead up the spreadsheet?
View 9 Replies
View Related
Sep 24, 2009
This module consists of app. 25 sub routines. Every sub routine is a For-Next Loop, with 70 For-Nexts to work through. The loops check certain input cells and if these cells are empty, the row is hidden. I have the reverse also where I can unhide these rows.
When I had about 8 of these loops in this module, it would work quite fast and it took about a second to run one of these subs. Now that I have 25 if I run one of these subs it takes about 20-30 seconds.
Before I break them all down into individual modules just to find out it doesn't work, is there anything else I should be doing or could try to speed these subs up? If breaking them down into more than one module is the solution, how do I do this best?
View 9 Replies
View Related
