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
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.
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
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.
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.
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?
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
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
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.....................
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?
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".
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
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.
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
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.
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?
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 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.
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
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
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?
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?
