Have an optimization question for you. I'm starting to try to optimize my macros and I've heard/read it's best to not activate or select anything. - I assumed that meant it would be more efficient to run code without it.
I have a loop I run through about 600 times that takes .75 to .85 seconds to run through with the following piece:
I have a macro, operating in my excel document. It works and does it's function. The only problem is every time this macro is running I get the run time error 1004, Method 'SaveAs' of object '_Workbook' failed. After clicking End in the error window it opens up a newly created sheet that I need. I don't need to save the file in the desktop, I need it just to open up like it does right now.
View 5 Replies View RelatedI would like to know how to write the macro that execute a formula everytime it detect an input in another column.
Attached is the example of what I want to achieve.
Macro to perform when entry detected.xlsx
I have the following code:
[Code] .......
What do I need to change in order to make it execute the Call statement on EVERY item in the ListBox2, not the Selections.
Optimize the code below to perform a search function for two variables. Right now it takes a while to execute as it goes through each cell row by row, column by column. It would be nice to optimize it to function a little faster (make that alot faster) . I realize my coding methods are not all best practice habits, so let me know your thoughts, workarounds or adjustments.
In its basic form the code pasted in below is being used to locate data at the intersection of a given row and column. And this data can lie anywhere within this array, unsorted.
Where the Row to search contains a team members name (Derek, John, etc.) D3:D50 and dates in columns Q3 to IU94.
My spreadsheet has a data page with a list of team member names in D3 to D50, and a listing of dates running accross Q3 to IU94. Each members name may appear muliple times through out D3:D50.
So for instance "Derek" (cell D6) worked 40 hours (cell Q6) the week of 9/1/08, it is the 40 that I want returned to a summary page and since the name Derek may show up more than once down the list I want to return all occurances where a date and name instersect on my summary sheet.
[/quote]
Below is my code which is currently used to lookup team member name "Lookup_Value1" for a given date "Lookup_Value2), it indexes the date column to search for data.
Function LOOKUP2(Lookup_Value1 As Variant, Lookup_Value2 As Variant, TABLE_ARRAY As Range)
Dim nRow As Long
Dim nCol As Long
nRow = 0
nCol = 0
With TABLE_ARRAY..............
Can anyone recommend a good source for tips on optimizing VBA code? I'm working on
an application that reads and interprets large text files (3500 lines) and starting to think
about the efficiency of the code inside my read loop.
For example I'm wondering how Excel actually implements access to cells internally. Is a
cell just a variable or part of an array?
If I do something like: range("A1").value = range("A1").value + 1 inside a loop is there a
performance penalty compared with saying: count = count + 1 and then writing this to a
cell outside the loop, eg: range("A1").value = count ?
I use a lot the function “Find” in Excel but the problem is that it takes a lot of time, so I’m searching for another function or code that can be faster than that, the Worksheets that I use in Excel contain thousands of sheets so it takes hours to execute the Macro.
View 10 Replies View RelatedLet me first quickly describe the workbook setup. I have a main workbook ("Fees") that acts as a master list for every employee's clients' account numbers. Every month, 30+ workbooks get downloaded from 3rd parties that have the client account numbers, as well as their current account value.
My macro- Loops through every employee's worksheet within the Fees workbook, and loops through every account number. It then compares the account to every account in every other open workbook. Upon a match, it pulls the account value back into the main workbook. After it finishes looping the Fees workbook, it starts to loop every open workbook, and checks every account value against every account in the Fees workbook. If it doesn't find a match, it prints the value on a Missed worksheet within the Fees workbook.
The situation- I know for a fact this a verrry slow way to go about what I need to accomplish. I am very new to writing code, and gladly can take the extra minutes to let the code execute to know 100% nothing was missed. Going forward, I would like to start trimming execution time without jeopardizing the 100% accuracy of my slow macro.
The question- Based on how I execute my loops, what is likely to trim the most time for the range lookups? I have no practical experience with Vlookup, but I understand that is a possibility I should look into. I'm vaguely familiar with Match, and arrays, and I believe they could also trim time. I also realize there are probably at least 15 other ways to go about it I am not even aware of.
I have 4 Loans of various interest rates, balances, and minimum payments.
Assuming I have a certain amount of money to pay out each month, how can I minimize the total amount I pay over the lifetime of the loans?
Given:
Total Monthly payment: M
Interest Rate for each loan: R1, R2, R3, R4
Initial Principal for each Loan: P1, P2, P3, P4
Minimum Monthly Payment: Min1, Min2, Min3, Min4
Each month, how should I distribute M over the 4 loans?
I'm using the following formula to calculate the % relative standard deviation of 5 values:
ROUND((STDEV(C8:C12)/AVERAGE(C8:C12)*100),6)
where C8 through C12 are all 3.48.
I was surprised to see the result of 0.000002, and not 0.000000. This formula seems to be accurate to 6 decimal places when using 5 values that are not all the same. When they are the same, I get this small discrepancy in the 6th decimal place. I attempted to troubleshoot by inspecting C8 through C12. The values were typed in as seen above (3.48) and the cell formatting is set to 2 decimals in each of these cells, so the cells themselves shouldn't be contributing to the issue.
In the attached worksheet, we track thickness of a variety of products and record them based upon lot number. I am trying to perform statistical analysis of those products and plot the averages.
Our products are in roll form. We cut to specific widths and lengths from these "master" rolls. We take 3 measurements across the width and several times throughout the roll length. We can have multiple rolls making up 1 lot and due to demand switch between products and try to use FIFO but that doesn't always work. I have keying in historical data and realized my standard deviation is way off but I don't know why.
time-to-time an unexpected eventcode appears in my worksheet modules
View 10 Replies View RelatedI've got a user form with 12 textbox, one for each month a total textbox number 13 and a 14th text box (TB28) to enter $'s in.
AfterUpdate in each textbox 1-12 posts its value to a worksheet all values are summed in a =sum range which in turn populates textbox 13. When TB13.text = 100.00 it sucessfully calls a number of routines but what I can't get it to do is set focus on TB28.
Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub
It returns a Run time error Unexpected call to method or property access highlighting .SetFocus. The rest of the code works OK. Is it because other textbox on the same user form have text highlighted because of the tab order?
I am using the find method to search column headings, and based on the results copy the column to another worksheet. Everything works fine except if I have a mixed text and numeric string in the cell, for example DT35. In this case the macro doesn't copy this column. I have attached the spreadsheet. CTRL - A will run the macro. The macro calls a form with checkboxes, captioned using the values in the worksheet titled "Set-Up". If a value is found in the column heading the checkbox is set to true, then when the "Copy Selected Columns to Final Sheet" button is selected the columns are copied to the "FinalSheet" worksheet. I tried using xlPart instead of xlWhole, and this works but I need to search for exact strings so xlPart isn't a great work around.
View 4 Replies View RelatedUsing this formula for a bank account,
=IF(SUM(C2-A1+B1)=C1, "match", "NO match")
down a column of about 3000 rows, yields only one cell with an unexpected and probably incorrect response in G2 with a "NO Match" - where the math is correct ?
A B C D
63.58 0.00 -54.97 match
64.57 0.00 8.61 NO match
128.21 0.0073.18 match
201.39
Details:
Attachment is included;
temp2.xlsx
in a bank account file, where "C2" is the previous balance, "C1" is the current balance, "A1" is any check written, "B1" is any deposit applied.
In the attached file I used =MATCH(TRUE,INDEX($B$2:$B$10="",0)) to retrieve the location of the first empty cell.
When using the "Evaluate Formula" tool, it is clear that the position in the array created by the INDEX function is the 6th. Nevertheless, the final outcome is 9, being the last cell in range.
If I create a simple conditional statement in a cell:
=IF(A8="NX-QSNT",B8, "")
I get the expected B8 cell contents when "NX-QSNT" exists in A8, or blank when it doesn't.
If instead of qualifying this full string, I try to Search for the "-QS" string in that cell as the criteria:
=IF(SEARCH("-QS",A8),B8, "")
I get the expected B8 cell contents when "NX-QSNT" exists in A8, but I get "#VALUE!" if it does not find this "-QS" string.
I'm guessing it may be because SEARCH isn't actually returning a TRUE/FALSE response, but rather, a numeric one based on the position of "-QS". I tried using a numeric approach also, but this didn't help:
=IF(SEARCH("-QS",A8) > 0,B8, "")
This relates to this thread - [URL] .....
This is the only macro in this file
[Code] .....
It will perform the sub-totalling for the column that has the current active cell
When I select Column I
It does.....
-for each blue cell it finds it provides a total of all the white cells bellow it
-for each yellow cell it find it provides a total of all the blue cells bellow it until it reaches a yellow cell
It works backwards, so not exactly as I've just described, but that isn't the problem
Problem is -
UK Excel 2010 - results are as expected
US Excel 2010 - returns zero values for totals
We've possibly narrowed the problem down to when it looks at cell properties, more specifically -- If Cells(rowX, 1).Interior.ColorIndex = 20 Then
How to get results in blue and yellow cells when you select a cell in column K then execute the CreateTotals macro.
Attached File : Example-1p.xlsm
I have a little spreadsheet aplication which ran perfectly in Office 97, but since the move to Windows/Office XP, I get the message:
Solver: An unexpected internal error occurred, or available memory was exhausted.
I'm running a P4 2.26GHz with 512MB RAM.
My code is as follows:
Public Processing_Message As String, Macro_to_Process As String
Public StartTerm As Integer, StopTerm As Integer, StepTerm As Integer
Public ResultsLabelCount As Integer
Public myErrorFound As Boolean
Public rs As Object
Public ctl As Control
When I close one excel document, I get prompted for a password.. And it won't close (I don't know the password) The only way I can get it away is by terminating it in task manager. I have read alot of threads in here, where the solutions should be to uninstall Google Desktop..
View 5 Replies View RelatedI am running Win XP and Excel 2003.
I have a macro I found here on the boards written by Lenze to delete an entire row based on what is found in column A. I would like to delete any row where Col. B contains 10 or less characters and I have modified it to do so (or at least I think it does). My problem is that it takes about 12 minutes to run the macro (I have about 50k lines to run through). I was wondering if this is the fastest method or if it examines things other than just column B.
Sub Test()
Dim i As Long
LR = Cells(Rows.Count, "B").End(xlUp).Row
For i = LR To 2 Step -1
If Len(Cells(i, "B")) < 11 Then Cells(i, "B").EntireRow.Delete
Next i
End Sub
After this runs, I am left with Columns A to somewhere around AH. The columns are generally in the format of text followed by a numeric column. An individual text column has the same name through all of the rows. The numeric columns have varied values whether negative or positive.
Ideally what I would like: If a given cell (ie. C2) in Row 2 is numeric, then copy the cell to the left (ie. B2) into (ie. C1) and then delete Column B. I need this to work for multiple columns from B to C, skip D and E, and then from F to AG (and maybe beyond).
This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.
View 2 Replies View RelatedI have below as part of my
Dim ToPath As String
ToPath = "C:Documents and Settings" & Environ("UserName") & "Desktop"
With Application.FileSearch
.LookIn = ToPath
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
Sheets("Sheet2").Range("A1:A65536").ClearContents
For i = 1 To .FoundFiles.Count
tempbuf = .FoundFiles(i)
tempbuf = Mid(tempbuf, InStrRev(tempbuf, "") + 1, 255)
Sheets("Sheet2").Range("A1").Offset(i, 0).Value = tempbuf
Next i
Else
Exit Sub
it exits the sub which apparently means .Execute is not > to 0.
So my question is, what does the line If .Execute() > 0 checks?
I am 101% sure that there are pdf files under the ToPath folder.
I'm using code from the Ozgrid page: http://www.ozgrid.com/VBA/loop-through.htm
However, once I get to the if statement If .Execute > 0... it does not contain a value. The folder contains 16 files in it, but .Execute does not recognize any of them. Is there a specific library or call that I'm missing??
I've been battling this for sooo long, I just dont know what to do with it.
Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01 (or whatever time I pick) for a stock market trading program, which is why the exact time matters so much. I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up in a cell as a constantly updating value.
I've tried using the Workbook_SheetChange function and OnTime method, but without luck.
In both cases, unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact, so I can't trust a macro scheduler to do this.
The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the following
Attachment 299651
I have turned a Workbook with Macros into an add-in. The macros work perfectly for the intended purpose: copy selected data in a special way and put it into an e-Mail message.
I used the CustomUI Editor to assign a macro to a Ribbon button.
However, when I turn it into an add-in and click the Ribbon button, it doesn't work on my machine or any other machine, with an error that says "Cannot run the macro 'CreateListofInventory'. The macro may not be available in this workbork or all macros may be disabled.
My macros are not disabled, and the add-in doesn't even work in the book where the macros reside.
I have two userform one if useing the getopenfilename to popluate a listbox. and then i use these listbox item withen this code to copy the files into a new folder. This works ok.
Then i copy a program into the same folder jpegresize.exe this program makes resized images of all files in its directory. by defult this program should only deal with these files. though what is happening is instead of using the files in the new folder it running on the files where the originals where copied from. (hope that makes sence). Though if i run jpegresize.exe from the new folder or command prompt it. It runs like it should. So i cant figure it out is there somethink the the shell and wait is passing that it shouldnt be.
I would like to execute a sub when the workbook is saved, what is the procedure for this.
View 3 Replies View RelatedHow do I have a workbook execute VBA code when I hit 'Enter' anywhere on a specific sheet? I don't need the code to execute when I hit 'Enter' on any of the other sheets in the workbook, just a specific sheet.
View 11 Replies View RelatedI coudn't find anything on this forum on this subject.
Is there a way to execute [trigger] a macro from within a formula?
e.g. Based on an IF statement result, execute macro1 if true or macro 2 if false?
modytrane