Log Macro Process

Jun 30, 2006

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.

View 4 Replies


ADVERTISEMENT

Process Of Executing Macro

Aug 30, 2009

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.

View 5 Replies View Related

Process Every Nth Row In Macro Loop

Jun 19, 2009

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?

Range("A3:V3").Select
Selection.Cut
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
Range("W2").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=-1
Rows("3:3").Select
Selection.Delete Shift:=xlUp

View 4 Replies View Related

Macro Progress Bar To Show % Process Done

Oct 25, 2008

As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....

I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?

Or even tell me which sub routine it is up to? (but % would probably be better)

And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...

View 14 Replies View Related

Macro Stop The Delete Process

Oct 12, 2007

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

View 9 Replies View Related

Select/process Variable Range With Macro

Dec 15, 2006

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.

View 9 Replies View Related

Make Macro Wait For Process To Complete

Feb 4, 2010

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?

View 14 Replies View Related

Running A Macro Process On Each Sheet In A Workbook

Jan 27, 2008

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.

Can you assist me in writing this macro.

View 9 Replies View Related

Batch Process 1 Macro In Multiple Files

Sep 12, 2007

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

View 3 Replies View Related

Select Multiple Files And Have Macro Repeat The Process

Sep 7, 2012

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)

[Code] .....

View 4 Replies View Related

Speeding Up Macro Process (Graph Algorithm Stuff)

Jul 7, 2009

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.

View 14 Replies View Related

Macro: Process The Cells For Every Worksheet In A Book Rather Than Just The Active One

Apr 2, 2007

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

View 9 Replies View Related

VBA Macro To Process Data After Paste With Text To Columns..

Mar 24, 2009

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
'

'
ActiveSheet.Paste
Columns("A:A").Select

View 9 Replies View Related

Macro Will Not Work To Process Files Stored On Network Drive?

Aug 1, 2014

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

[Code].....

View 7 Replies View Related

How To Process Checkboxes In VBA

Feb 11, 2014

how to handle checkboxes in VBA

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

View 6 Replies View Related

Process Before Printing

Feb 2, 2010

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.

View 2 Replies View Related

Automate Process

Feb 22, 2007

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.

Could this be done by date formula?

View 9 Replies View Related

VBA Process For Multiple Actions

Jul 17, 2009

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.

View 5 Replies View Related

UserForm Won't Process Because VBA Is Too Long

May 9, 2014

I have a UserForm where the user can enter figures for 143 different items which they select via a combobox drop down.

When I tested it, I hit the submit button but it says the procedure is too large!

Here is the code which is repeated 143 times but with a different row number and range each time (in red):

[Code] .......

So the next bit of code would be exactly the same except the range would be C5 and the row would be 18.

The range is always going to increase nice and easily: C5 up to C146.

The row number is a bit hit and miss.

Is there a way of significantly decreasing the size of this or will I have the break the code down into different UserForms?

View 14 Replies View Related

Interrupting The Save Process

Oct 24, 2008

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

View 5 Replies View Related

How To Repeat Process For Rows

Feb 18, 2013

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

View 3 Replies View Related

Process Without Having The Program Crash

Feb 7, 2007

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.

Here's the formula I am using.

=(SUMPRODUCT(($I$2:$I$65536>=$D$2)*($I$2:$I$65536

View 9 Replies View Related

Phantom Process With Remote VB6 Dll

Jun 21, 2008

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.

View 9 Replies View Related

Process Append Query Through VBA

Dec 1, 2009

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

View 9 Replies View Related

Create And Process Dynamic Form?

Feb 9, 2014

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.

View 4 Replies View Related

Data Validation - Automating Process

Feb 12, 2014

I'm currently trying to automate the process of creating Data Validation.

PFA.

I have Grades and Names List.

" GradesList " Range has values "One, Two, Three, Four "

I'm trying to map Names List to GradesList using INDIRECT().

Debug the function DataValidation() to understand the issue better.

View 7 Replies View Related

Selection Process Finding X From Within 7 Columns

Sep 10, 2009

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.

View 14 Replies View Related

InputBox And Cancelling For Stop The Process

Sep 29, 2009

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.

View 2 Replies View Related

Process To Combine Set Of Numbers That Have Range Of 1 To 48

Jul 20, 2012

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.

View 3 Replies View Related

Automating Copy And Pasting Process?

Oct 24, 2012

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.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved