Structuring Code To Efficiently Process Data
Aug 23, 2009
I have a two workbooks, one which is a daily schedule and the other is a yearly summary. The worksheet in the yearly summary uses a date in the first column and the daily sheets each have a date on them. I've isolated my data in the daily schedule and I've retrieved the date in its numerical equivilant. I have data that is 6 cells wide by "x" amount deep on any given day. Essentially, I schedule different things to be made and each row has that designation, process, dimensions, quantity, etc.
I want to test the contents of the first cell in the array, copy the pertinent data, switch to the proper worksheet, and paste it into the line where it goes. I believe I need to check a few cells to narrow down exactly which group of cells on which sheet they would be copied to.
I am not trained at all as far as programming goes, but I've been practicing for a few years now. What I am just going to start doing is testing the cells in the rows in the array of data and trying to divide it out that way. This just seems like the long way around.
View 2 Replies
ADVERTISEMENT
Jul 26, 2009
I have two macros one of which imports the data and the other processes the data. These macros were created at different times and need to be joined into a single macro that will combine their operations – with slight modification to the importing macro. Let me describe what each of the macros does:
PROCESS macro:
Cycles through the CONTROL CELL on the “1” tab using the VALUES TO TEST. For each value to test it copies the values form the output tabs and pastes them into the A+B tab. Then it removes the duplicate rows there.
Option Explicit
'======================================
Sub PROCESS() 'This subroutine is called when clicking on the first button
Dim i As Long, j As Long, k As Long 'Those are the variables that will browse through the whole sheets
Dim l As Integer 'this will be used to browse through the values to test
Dim TheSearch(1 To 2) As Object 'This is an array of objects that will be used for the search
Dim TheRange As Range 'This will be used to flag the range of duplicates in the output
'Searching for the Text string 'VALUES To TEST' in the sheet "1" in order to know where the array of values to be tested start
Set TheSearch(1) = Range(Cells(1, 1), Cells(ActiveSheet.UsedRange.Rows.Count + 10, ActiveSheet.UsedRange.Columns.Count)).Find(What:="VALUES TO TEST", LookIn:=xlValues, Lookat:=xlWhole)
View 9 Replies
View Related
Sep 8, 2006
write code to post Excel data directly to Peachtree using VBA rather than the export/ import process?
View 5 Replies
View Related
Jan 22, 2013
I have an auto generated report that is exported into .csv, my dilemma is that it sorts both the account code and the date in the same column, like this:
9884A B Gerrish Ltd Å“ 577.270577.270
30/09/201231/10/20121865518655277.010277.01MAL/553260
30/09/201231/10/20121884218842264.260264.26MAL/553259
30/11/201231/12/2012195541955436036MEL/549200
so "9884" and "30/09/2012" are in the same column.
Some how i need to turn this information to say the below three invoices are for account 9884, into more of a usuable data like:
9884 A B Gerrish Ltd 30/09/2012 etc etc.
Obviously through out the document this repeats for each account, upto some 1000 accounts.
jas.xlsx‎
View 11 Replies
View Related
Mar 5, 2008
so i'm building an application that'll allow users to manipulate records in Excel with just a GUI, using Userforms, Modules and Class Modules. it's all working but i'm feeling like i skipped a little on structuring it properly.
for example (from my Java work in college), you'd call just one object from the main method, which would create a GUI object, and create/manipulate instances of the different Classes when buttons are pushed. basically you one object whose main created other objects, who ran procedures, etc. what i'm hoping for is to make it as modular and easy to maintain as possible. would anyone have a good resource for optimising a medium-sized application? (the tips Excel/VBA Golden Rules. These Should NOT Be Optional were very good, by the way.)
View 3 Replies
View Related
Apr 20, 2014
Here is what I am trying to accomplish - note that I need the formula to exist in an unrelated cell - in this example I use A6
Cell A1 is a drop down with "Proposed" and "Actual" as the choices.
Cell A2 allows user input ("Proposed") until the "Actual" value is known.
The "Actual" value is pulled from a different workbook (Workbook2).
The "Proposed" number can change many times before the "Actual" number is pulled in...this means that I cannot place the formula into A1 as it will be overwritten when the user puts in their "Proposed" number.
I would like to place the formula into an unrelated cell to continue to allow A1 to be user edited.
Formula would reside in A6 and the statement I am trying to execute would be something like:
If(A1="Actual", A2=Workbook2.xlsx]Actual!A2)
I have tried the formula in it's simplest structure to confirm it works before I reference workbook2 but it doesn't seem to work (formula resides in A6): =IF(A1="Actual",A2="hello"). I just get a "FALSE" result in A6. If I remove the = before IF excel does not recognize the statement as a formula.
View 1 Replies
View Related
Apr 14, 2014
I am extracting data from an electronic medical record (EMR) which has many search limitations. I am able to export data into a CSV file (attached) to perform some calculations (All the patient data is fake btw). I am trying to find the number of appointments for each patient within a specific date parameter. For each patient, one appointment appears on one row.
pt appt sample.png
What I want to do is count the number of rows (appointments) for each patient in an efficient manner. Right now I've just used the COUNTA function, but don't want to repeat this for every patient, due to the way that the workbook is set up. The data is all from the CSV file from the EMR; the only column I've added is the "Appointments since 2013" column.
The patient data is fake because I am using a test server for the EMR. However out in a real doctor's office, there will be over 2000 patients with possibly up to about 100+ appointments.
Not sure if there's formula out there that would make my task a lot easier and more efficient, or I need to play around with the columns to set it up differently.
View 6 Replies
View Related
Apr 8, 2008
In the attached work book I have 2 sheets named, Data Entering page-plan and Varaince Summary-in hours.
Data Entering page-plan - is used to enter data
Varaince Summary-in hours. – is used to retrieve data
In ‘Varaince Summary-in hours. sheet in Cell A6 I have a drop down to select the job nos, according to the job no selected date, position no, and milling hours are filtered and displayed. I have written a index/match formula in G6 and have dragged it downwards since I don’t know how many positions would be fetched in each job no selected. Is there any way not to display N/A in unused cells in Column G each time a job no is selected ?( embedding the function in a IF using ISNA or ISERROR would be inefficient as I feel since this sheet is going to house lot of data of about 30 sections, I have shown only one section here)
View 4 Replies
View Related
May 17, 2014
I have a large number of files that are structurally the same - only the data and the file names are different. I want to insert some code into all of them.
I don't yet know how to open all the VBA pieces together at one time for editing. Is that possible? Otherwise all I know how to do is a manual, file by file, process.
View 9 Replies
View Related
Mar 7, 2008
I've managed to clean up my code removing all of the selecting to get the macro to run faster but it seems to slow down when it gets to the page setup. Is it because of the ActiveSheet? Is there a better way to code the following?
View 10 Replies
View Related
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
May 18, 2014
how to process this non-numerical. I tried to use formulas but it wasn't fruitful. refer the table and.
Name
English
G
Maths
G
Science
G
History
G
Aggregate
Result
Achievement
[Code] ......
****The aggregate derived from the grade of the subjects , where each grade will be given a pointer and the average pointer for all four subjects will be considered as aggregate :
Pointers for grades
A =1
B=2
C=3
D=4
E=5
This is how the aggregate for Alex should be counted:
English A = 1
Maths C = 3
Science B = 2
History A = 1
*****aggregate = (1+3+2+1)/4 =1.75
The result column be showing Pass/Fail. If the student fail one of the four subjects he is considered as Fail.
**Grade D and E =>Fail
The Achievement Column should be indicating the numbers of As, Bs and Cs
for example: O6= A(2),B(1),C(1)
View 7 Replies
View Related
Dec 21, 2006
I have a database which contain many rows and columns. I tried using Pivot Tables to pull out the data from sheet 1 to look like the format in sheet 2, but without success.
View 5 Replies
View Related
Jul 8, 2014
The report that I am trying to build relies on DCOUNTA formulas.
This report has 200 lines and 200 columns approximately. Each of these cells would be a DCOUNTA formula. I have a separate sheet, 'Criteria', where I intend to describe all of my criteria for the DCOUNTA formulas.
My problem is that for each DCOUNTA cell in my report, I have to create 2 rows and 5 columns worth of criteria information... so, for 40,000 cells to populate, I need to create a huge sheet of criteria.
So, while I could do the above, and it would take me some time, more efficient and elegant way to spend my time in order to get this report display DCOUNTA formulas?
For more context, I am trying to present information for 100 individual salespeople, monthly and year to date. For each cell, I need to tell DCOUNTA that the date needs to be comprised in the current period (2 columns), what the name of the salespeople is, and what the nature of the information is, which could be more than 2 additional columns.
I can to some degree autofill most of this, with some OFFSET magic, but its still feels like an ugly way to achieve my objective.
View 9 Replies
View Related
Nov 28, 2012
From a large set of process data, I would like to copy those instances where there is indeed a measurement available to a different sheet automatically.
The original data looks like this:
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:42:50
02-Jan-2010 11:43:50
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:45:50
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:49:50
02-Jan-2010 11:50:50 157.2
The resulting data shoot look like this (in a different sheet):
Time Temperature
02-Jan-2010 11:41:50 156.1
02-Jan-2010 11:44:50 157.3
02-Jan-2010 11:46:50 156.8
02-Jan-2010 11:47:50 156.4
02-Jan-2010 11:50:50 157.2
I have actually been learning quite a few tricks on my own since I started this data analysis project but I believe this requires some experience with macros, which I really don't have.
View 2 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
