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'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.
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
I have a worksheet ("Sheet1") with an activex checkbox (CheckBox1) in cell B1
what code do I need to tell if the checkbox is ticked or not. (it probably should be some true / false type test but I keep not getting the syntax of the code correct.
what do I need to declare or set or ? to access the state of the checkbox
I need to print a worksheet. The sheet has rows of a specific height (45) and data in one cell grows - adding comments/notes with date attached so that last one entered is displayed, the rest get shoved down.
I need to sometimes print out the sheet. However, I want to have all the notes shown so would like to (1) autofit all rows, (2) print the sheet and then (3) put all rows back to row height of 45. All this I would LIKE to have done by "trapping" the print button on the normal Print area
I have attached some code, but the sheet (1) does not print at all, (2) the code is sometimes executed twice (in debug mode followed it) and (3) sometimes does not set the row height properly either at autofit or static height.
Dont know if this is possible. I have a spreadsheet where cell A1 contains a value which is not fixed and can change daily, the value can increase and decrease. At present at each month-end I manually record the position of cell A1 at month end in cells C1 through C12 repsenting the 12 months of the year.
Example: A1 @ 31/01/07 = 50 therefore cell C1 = 50 A1 @ 28/02/07 = 45 therefore cell C2 = 45 and so on for 12 months
what I would like to do is automate this process to automatically capture the value at month end into cells "c" and once captured, the figure to remain absolute for that cell/month and to ignore changes in Cell A1 that may subsequently occur.
I have a list of petroleum accumulations with three types of data: Reservoir depth, Net Pay and Gas-oil ratio. for each accumulation, the number of reservoirs within each can vary. So I may have a accumulation with only one set of those stats, or an accumulation with 20.
I need the program to go down an alphabetized list (8000 entries long) and after every unique accumulation name average the values of the stats(which are in three separate columns) then put it in one row perhaps beginning in Column M or another sheet. So that I have a list of accumulations with the average of those stats, one entry per accumulation name.
Is there a way I can interupt the save process depending upon certain conditions. For example if a particular cell contains a specific value and the user attepts to save the worksheet I would like to interupt the process asking "Do you really want to save this sheet now?" with the options to continue or abort. I think I can manage the message box or userform but don't know how to initiate it when save is selected
I have a list of rows with Yes and No check boxes. Columns "T" and "U" contain the output of these checkboxes (i.e. "TRUE" or "FALSE"). At the end I have a button which I'd like to run this code. Essentially, I want to code to go through row by row and do the following:
1. Check to see if both boxes are blank or if both boxes are checked - and if so set the background color to yellow 2. Otherwise set the background color to clear
I have written the following code which accomplishes this task for Row 9. Is there an easy way to repeat this process for rows 10-15 without copying all the text and changing the row numbers?
Then, any way, say "If all the rows (i.e. 9-15) have clear background colors (which would mean that they all 'passed' the test of having exactly one and only one box checked in the row), then run another macro which I have written"?
Rem Check to see if neither box is checked or if both boxes are checked and sets background color to yellow, otherwise sets background color to clear.
If (Range("T9").Text = "") And (Range("U9").Text = "") Or (Range("T9").Text = "TRUE") And (Range("U9").Text = "TRUE") Or (Range("T9").Text = "FALSE") And (Range("U9").Text = "FALSE") Then Range("B9", ("G9")).Interior.ColorIndex = 6 Else: Range("B9:G9").Interior.ColorIndex = 0 End If
what excel can process without having the program crash. Our company is using an old dos program to store payroll information (don't ask -they won't change it). Anyway - I have the ability to use pervasive software to pull the information into excel.
The problem here - is that the information is stored line by line. And in order for me to get useful information out of it, I need to sort those lines by date and a task code.
I remoted most of my number crunching stuff to a dll.
It work fine except for a few strange things that I observed like from time to time a strange message "Error in loading DLL" even though everything works fine. This message appears only when entering the main formula in the formula, and is no problem at all in usual work.
When I investigated, I noticed in the "windows task manager" that one instance of excel remains open even after I have closed excel. This phantom excel process is maybe related to the other problem I explained above.
Instead of calling a VBA function, it creates a VB6 object from this dll and uses the methods of this object. In this way I have remoted the number crunching functionality.
The main parameter passe in this process is a reference to the original workbook. I need it because the number crunching functions need to read data from the workbook.
I made it sure to terminate the life of the object properly on both sides by ad hoc obj=Nothing statements.
Still I think that the "garbage collection" or the "terminating" is the cause of the problem.
I currently have a great VBA option to append records to the appropriate Access table. One problem I'm running into though is getting by the unique record number. Currently a user submits a record to a DataStorage tab which is then appended to the database. However, we would like to keep some of the old records (maybe a weeks worth: approx 100 records) on that tab in case any problems arise in the append transition. But we don't want duplicate records in the table.
Is there a way to modify the below code to kick out the duplicates and only bring in new records to the table? I will assign the table the requirement to only bring in unique new records but the code errors in Excel.
Sub RunAccessQueries_ADO() Dim cn As ADODB.Connection Dim cm As ADODB.Command
dbPath = "C:Documents and SettingsUSERDesktop" dbName = "MyAppendTest.mdb"
Set cn = New ADODB.Connection Set cm = New ADODB.Command
With cn .CommandTimeout = 0 .Provider = "Microsoft.Jet.OLEDB.4.0;" .ConnectionString = "Data Source=" & dbPath & dbName .Open End With
With cm .CommandText = "MyApp" .CommandType = adCmdStoredProc .ActiveConnection = cn .Execute End With cn.Close ActiveWorkbook.RefreshAll MsgBox ("Append Update is Complete") End Sub
I want the user to type data via a form, but the number of the records to be added may vary.For this purpose I want to create a form, which has a "new record" button, which adds a new textbox, checkbox and radio button to the form. I do not really know, how to add these controls on such a way to the form, that the new elements will be well aligned below to the existing ones and the size of the form will also be adapted if it is necessary.
Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.
Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.
I have the following Input Boxes. If cancel is pressed on any box then I want the process stopped. eg if I enter an answer on sya questions one or two then click Cancel on question 3 I want the process stopped.
I need a process to combine a set of numbers that have a range of 1 to 48. The set can vary. I need all possible combinations. Example:
out of the range 1 to 48, the set of numbers are (6,11,15,21,22,27,33,34,47). The numbers need to be combined into all possible combinations of subsets of 6 numbers. The criteria for combination is does not include sequencing. In other words the numbers only need to combined into 6 number sets that are in any sequence.
I was wondering if there was anyway to automate this process. Say take all the files in this folder copy visible cells on a specific tab and paste them into a new worksheet/model we already have built. They shouldn't be consolidated rather pasted below the next.
