Data Handling ....
I'm currently working on that requires me to compare and update between two different workbooks, "MyData" and "Daily".
As the names imply the first is an ongoing database the second "Daily" is upto date information that arrives each morning. In both workbooks there is a column that contains unique data/serial number.
I have written some code that compares these numbers, if they are the same the new upto date information overwrites the original information (replaces entire row), if the serial number is new it adds it as a new entry in the last row.
Well so far so good, actually I have only managed to do this on the same worksheet, but it does work.
As there is a lot of data I'm looking for the fastest way to "look" for this information should I compare between the two open workbooks line by line, should I copy all of the data from the "Daily" to a worksheet in "MyData" and work from there.
View Complete Thread with Replies
Related Forum Messages:
Handling Gaps In Time Series Data
I have been browsing here off and on, and have found many excellent answers. I use Excel to process data on time series, as an adjunct to consultancy work on statistical analysis of industrial data . Usually the data has irregular gaps, e.g., daily data might have 2-10 day gaps. If I want to take, say, 7-day averages, SKIPPING OVER gaps longer than 2 days(say), is there an easy way to do this (I don't really know VBA,and it is not worth my time to try and write long code for this, which will eventually be done by some professional programmers)!
Error Handling For Data Validation List
I have a excel document that is very heavy with Data Validation List Boxes. Below is just one example. Error handling is what I am trying to figure out.
I have 3 List Boxes.
One list box data is based on data selected in the previous list box.
1st List Box uses this formula: =Series
2nd List Box uses this formula: =INDIRECT($C$2)
3rd List Box uses this formula: =INDIRECT($C$3)
It all works! HOWEVER....
If user decides to go back and change selection in first or second list box, and forgets to change the data in the second or third box, then data will be incorrect. How do I solve this problem?
Is there anyway to write something so it would give them an error message and instructing them to correct the data?
Spin Button Event Handling
The issue I'm having is with the ActiveX Spin Button (in 2007, formerly in the Control toolbox in 2003). I need the Spin Button to call a certain macro whenever it is clicked up or down. I know how to do this with one spin button, the problem is that the workbook I'm creating could potentially have dozens (or even hundreds) of these spin buttons that all need to call the same macro. It doesn't seem feasible to have to create separate Change event functions for each of these.
My company used to use the simpler form controls for this process, but a situation has come up where we need the ability to make the buttons invisible, or at least appear disabled, and that doesn't seem doable with the form controls.
So my question is, is there a way to specify in my workbook that whenever a spin button is clicked, this particular macro is to be called? Or even, whenever an ActiveX object is clicked, call the macro, because the spin buttons will be the only ActiveX objects in the workbook. I've read a bit about how to create global event handlers for worksheets and workbooks, but I can't find anything related to spin buttons specifically.
Error Handling Of Advanced Filter
If there are records meeting the criteria are found, they are to be copied to a 2nd worksheet. Otherwise, the remainder of the code is to be executed.
I am getting an error "No Cells were found" (which is true since the autofilter doesn't net any results) with the line highted in red.
Consider this snippit of
Dim lcrtcopy As Long
End_RowDest = 0
If .FilterMode Then .ShowAllData 'disable advanced filter of data if applicable
VBA - Error Handling: Determining The Sub Procedure
In an error handling procedure, I want to be able to capture the name of the Module and Sub procedure (macro) that the error has occurred in.
Is there any way to do this, outside of storing the name of the Sub somehow in a variable?
The Err.Source method seems to only contain very broad information, such as "VBA Project" when an error is encountered.
Error Handling (Close Without Save)
I've got some code that reformats a file that a supplier sends us. They seem to randomly change the structure, so I'm trying to make the code a bit more robust.
Part of the code uses MATCH to look for specific column headings, so I've got some error handling around this of the format:
On Error GoTo NoIncCol
Line using MATCH
On Error GoTo Exiting
where Exiting is my standard error handling (Close without Save) and NoIncCol is the same, but with an error message saying that the term the code was trying to MATCH can't be found. This works well for me.
In another part of the code, I need to check for another column heading, which so far has turned up in two different forms. At the moment I'm doing this as follows:
Set rFound = .Find("Term1")
If Not rFound Is Nothing Then
a = "Term1"
This feels a bit clunky, and I don't really like having what is essentially error-handling in the middle of my code. But the only other way I can think of to do it is to use a GoTo, and I believe that's not really considered good practice either.
Error Handling Using Cells.find
The On Error GoTo works if there is only one instance of the text not being found but crashes if there is a second.
I have tried adapting code found here http://www.excelforum.com/excel-prog...ght=cells.find but can't get it to work exactly as i would like.
Here is an extract of my code. There are about 12 different cells.find in total.
Error Handling With Find Method
I have a UserForm (and associated code) to locate a specific entry in Column 'A' of a spreadsheet and insert a date, initials, and hyperlink to another file.
The basic code works fine, but did not account for the user entering a Job# that was not in the system, so I added an If/Else to account for this...
The additional code took the If (Job# not found) route every time...
Following many hours of searching this site and variations of the error handling code, I still can not get it to work.
So I'm asking for your assistance to point out why the error handling (based on other successful code found here) is not working for me.
Private Sub CommandButton3_Click()
If TextBox1.Value = "" Then
MsgBox "Please Insert a Job#"
ElseIf TextBox2.Value = "" Then
MsgBox "Please Insert Quoters Initials"
ElseIf TextBox3.Value = "" Then
MsgBox "Please Insert Quote Date"
The 'Find' part of the code is working, as the correct cell is selected after running the code... but it gives the error message, instead of executing the rest of the code...
If I remove the 'On Error Resume Next', it stops on the find block, where on inspection FindR = Nothing.
So if FindR does = Nothing, how did it manage to select the cell?
Handling Non-Unique Cell Values
I have attached a sample spreadsheet. I'm looking for a macro that will take column one which contains Company names which are sometimes duplicated and append an incremental number based on the number of occurrences to the end of the company forcing it to be unique. Column 2 has the example of the results I would like to see. Has anyone got a macro that I could use that would allow me to select the company column, run the macro, and it update the values in that column to append the incremental number?
Application-wide Error Handling
I am creating a rather large system that is as OOP as I am able to with VBA (Please let the next version of VBA use .NET!).
I have created an ErrorHandler object that deals with all logic/user errors and I have decided to include runtime errors in it's scope as well.
Now I have an object function to catch all errors as such:
Public Function CatchAll(E As ErrObject)
Select Case E.Number
MsgBox E.Number & ": " & E.Description & vbCrLf & E.Source
Now I have added the following to every single procedure and function in my application, and have realised how ridiculous it is.
On Error Goto ErrorHandling
Is there a way to add a single global ErrorHandling statement somewhere in my code that will be called whenever an error is raised?
Perhaps in my main module that fires all code, can I put a small procedure that will handle the errors? Or as a function in my ErrorHandler class that I call on initialisation?
Save As Error Handling Through Input Box
I need to:Turn off "save" and "save as" command barsPrompt user for a unique filename Save file to a specific directory on the network common to all "p:dataprc"Requery user if filename exists and if they DON'T want to overwrite.Return to worksheet (there is only one) if the user cancels from the message box.Turn commandbars back on The macro runs from an on screen button
I get bug errors on NO or CANCEL when clicked in the message box. This is my current
Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save As...").Enabled = False
Application.CommandBars("Worksheet Menu Bar").Controls("File").Controls("Save").Enabled = False
' Keyboard Shortcut: Ctrl+z
Dim BaseDir As String
Dim NewName As String
BaseDir = "p:dataprc" ...........
Event Handling For Controls Created At Runtime
I am having trouble working through the examples given on other posts for this topic. I have a userform that creates a number of controls at runtime. One group of controls is a series of textboxes and a label underneath that sums the values of the textboxes. When a user changes the value in a textbox the label needs to update the sum. I have read about created class modules to define the event but it is all going over my head.
A piece of the code that creates the controls is:
Handling Error If 2 Cells Are Accidentally Selected
what i could do to the code below so that if 2 cells are accidentally highlighted - it wont come up with a type mismatch error on the line below in red.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If .Value = "" Then
UserForm2.TextBox1.Value = "No Trade Selected"
UserForm2.TextBox1.Value = Format$(WorksheetFunction.RoundUp(Val(Sheets("price").Range(Target.Address).Value), 2), "$ 0.00")
Msgbox Doubleclick Resume Error Handling
The following code shows the bare bones which illustrate my problem. The routine runs whenever the user enters a string which begins "Frm1=" and contains exactly three commas.
My problem is with the MsgBox which appears if the ErrorHandler is reached. With the following code the MsgBox requires two clicks on OK to dismiss it. If I change Resume to GoTo it requires only one. Is this normal? Is there any way of dismising the MsgBox with one click? ...
Error Handling Within Errors (macro To Create New Worksheets In The Workbook)
I am currently creating a database which involves using a macro to create new worksheets in the workbook. When the macro is run and a work sheet is being inserted, an input box asks the user for a worksheet name.
As you can imagine, the worksheet does not like it when the worksheet name input by the user, is the same as one already existing and so throws up a 1004 error.
In order to resolve this error I have included an error handling code to request the user to input a differnet worksheet name, as the one previously inserted exists.
My problem: It all works fine until the user types in an existing worksheet name twice, so once initially and again when the error handler has prompted a second attempt. On the second incorrect input a 1004 error warning is displayed.
I would like the error handler to keep repeating until a worksheet name that doesn't exist is inserted by the user. Is this possible?
My code so far:
Sheets("Tranche Sheet Template").Visible = True
Sheets("Tranche Sheet Template").Select
Sheets("Tranche Sheet Template").Copy Before:=Sheets(1)
Sheets("Tranche Sheet Template (2)").Select
Sheets("Tranche Sheet Template (2)").Move After:=Sheets(Sheets.Count)
Sheets("Tranche Sheet Template (2)").Select.................
Using Class Module For Handling Events Of Dynamically Created Controls
I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.
I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).
Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.
HTML Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox
Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
Put In Error Handling In Case The Format Of The Page Changes Or The Internet Page Is Unavailable
I have some code that parses an html table. I want to put in error handling in case the format of the page changes or the internet page is unavailable. I am testing without an internet connection and the run time error is not being branched off to the error handler and is fatally ending the macro. This is true also if I raise an error manually.
Private Sub ParseInjuryPage()
On Error Goto ErrorHandl
Dim strPage As String
Dim webIE As SHDocVw.InternetExplorer
Dim myURL As String
Dim tableBeg As Long
Dim tableEnd As Long
Dim RowBeg As Long
Dim rowEnd As Long
Dim cellBeg As Long
Dim cellEnd As Long
Dim strBeg As Long
Dim strEnd As Long
Dim myCell As Range
Dim rowNum As Integer
Set myCell = .Range("A2")
.Range("A:F").Value = vbNullString
rowNum = 2
Set webIE = New SHDocVw.InternetExplorer
myURL = "http://www.sportsline.com/nfl/injuries"
Do Until webIE.ReadyState = READYSTATE_COMPLETE
strPage = webIE.Document.body.innerhtml...................................
Add Data Monthly And Then Have To Manually Update The "source Data" To Reflect The Added Data On Chart
I have a column line chart to which I add data monthly and then have to manually update the "source data" to reflect the added data on chart. This is a rolling graph, which mean that I have to remove data for one month(from last year) and then include the new month's data. Is there any way on automating this process...like a macro or something, so once I add the data excel automatically removes one month of old data and make changes to include fresh data. Eg Currently chart is based on data from A2:F2 and I add new data to cell G2. I need something which automatically update the source data to cell B2:G2.
Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet
I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used
.Fields.Item(2).Value = Empty
.Fields.Item(2).Value = 0
after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).
Copy All Data From Data Sheet, Sequence Top Down By Spend, Manually Delete All After 50
I have two sheets, a data sheet with all our customers by ref, name and spend; and a presentation sheet.
In the presentation sheet I want to display the ref, name and spend of the Top 50 customers by spend, price high to low.
Copy all data from data sheet, sequence top down by spend, manually delete all after 50.
My only issue is that each month the data sheet will update and I want the Top 50 to auto update, without performing the workaround above.
Is there a way to do this without VBA i.e. pivot tables etc
Extracting Data From One Massive Worksheet To Split To Smaller Sheets Of Data
10/05/0808:30:00 AMValid Card EntryLamDoor 1
10/05/0808:31:00 AMDoor Leave Open Door 1
10/05/0808:32:00 AMDoor Closed Door 1
10/05/0808:41:00 AMValid Card Exit Yap Door 1
10/05/0808:46:00 AMValid Card Exit Lam Door 2
10/05/0809:14:00 AMValid Card EntryLam Door 2
10/05/0810:18:00 AMValid Card Exit Lam Door 2
10/05/0810:19:00 AMValid Card Entry Yap Door 1
10/05/0810:40:00 AMValid Card Exit Yap Door 1
10/05/0810:42:00 AMValid Card EntryLamDoor 2
11/05/0808:30:00 AMValid Card EntryLamDoor 2
11/05/0808:20:00 AMValid Card EntryYapDoor 1
11/05/0810:20:00 AMValid Card Exit Lam Door 1
11/05/0811:40:00 AMValid Card Exit Yap Door 1
the above is the data i need to work with (dates are in dd/mm/yy). i need to find a way (a macro, preferably) to extract the date and time corresponding to one person, and paste it into a new sheet, e.g. at sheet 2 (renamed to "lam") should be
10/05/0808:30:00 AM 11/05/0808:30:00 AM
10/05/0808:46:00 AM 11/05/0810:20:00 AM
Converting Vertical Data To Horizontal For Varying Data - Large Amount
I have a one column spreadsheet. The column contains this data:
20 Address ... and so on
Where there may be one or two blank rows between the individual records and where there may or may not be a Fax number (or row) in the record.
I am trying to convert this data to a horizontal column format - which works fine if I do a copy/paste special/transpose. However I have to do this for 1,800 records and cannot figure out how to do this reliably.
I gave the above illustration to simplify but, actually this is a two column spreadsheet with individual row labels for every record using the above terminology. In other words the above text is in the first column and the data is in the second. Just thought I'd mention in case there was a way to do some kind of if/then formula.
VBA To Autofill Formula Over Column Data Calculate And Replace Original Data
Id like to apply a formula, any formula to an entire column if it contains data, and incorporate the original data in the calculation and then replace the original data with the result. I don't want to have to create new columns.
I'm using this to fix up database results; a common problem is dates in dot format e.g. 14.11.2008
All I have so far is an autofill formula that overwrites everything. Can someone help me with the rest? I'm using the SUBSTITUTE function to replace the dots '.' with slashes '/'
Dim Limit As Long
Dim r As range
Set r = range("A1")
r.FormulaR1C1 = _
Limit = ActiveSheet.UsedRange.Rows.Count
r.AutoFill Destination:=range(r, Cells(Limit, r.Column))
Color Chart Data Points By Cell Values Of Non-Source Data
I have a bar chart that was made from table 1 which has the value in it. I have another table, table 2 that has the name corresponding to the value in table 1. I want to color data points in a bar chart with a different color for each name I have in table 2. I also want to give a data label in the data points with the value I have in table 2 instead of the original data label from table 1. Is it possible to do it?
Copy / Filter Data Based On Data In Column Occuring X Times
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
Press Button To Open Form For Data Entry And Then Send Matching Data To Another Sheet
Now that the calculations are working, with the press of a button, I need to be able to select a range of dates and copy all lines within the range to a seperate sheet with the desired name under the same headings they currently reside under. I have included some modified code that is being used in another spreadsheet that was created for me, but I do not pretent to understand all of it and I no longer work with the creator of the spreadsheet. How do I use a button to open the form for date selections and entering the name of the new sheet, and then use the start button on the form to begin the matching and copying to a new sheet? If there is an easier way I am all for that too.
Index Function - Data Identified Based On A List Of Account Numbers And Return This Data To A New Sheet
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
Delete Adjacent (hidden) Data When Deleting Visible Data
I have 3 columns of data that are being copied from a .csv file. The data is pasted into Columns B, C, & D. Columns C and D are hidden. So that the user does not have to unhide the columns in order to delete the data, I would like to make it so that when the data in column B is deleted, the data in columns C and D is also deleted.
VBA Program For Data Entry - Find The Matching Data To Update
I want the script to find if the value entered in the form is matching the values in column 'A' in the database and if it matches then it needs to select the cell as active cell - to populate the form details. And if there is no matching value found, the script needs to select the last empty cell of the column 'A' to populate the data entered in the form.
1) Form has 10 different fields that needs to be filled by the user.
2) Field 1 - is a text box for 'Request #' to be entered by the user.
3) After filling in all the fields - once clicked on OK, the form should search for the the 'request #' entered on the form in the database (Form and the database are in the same workbook).
4) If the 'Reqeust #' in the Column 'A' matches the the 'Request #' entered in the form, then the matching cell should be selected (Activecell -Were the data can be overwritten, with the new entry)
5)If there is no matching 'Request #' found in the database, the script should loop to select the next available blank cell in column 'A'. So that the form data can be entered.
Copying And Pasting Data Scenarios Into A Data Report Sheet
Here is my situation:
X Y Z
Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep
The above format is how I want my spreadsheet to look like. On another sheet within the workbook, I have a chart with some control options that allow me to vary X, Y, Z (I can vary either of these individually). Anytime I vary any one of X,Y,or Z, then the values in A,B,and C all change. This is what I am looking for, a Macro that will allow me the opportunity to everytime I change or vary an option (X,Y,Z), I can hit "Save Scenario" and the new values will be appended to my "Report" sheet. The values of A-C and X and Y are kept on a sheet entitled "FY 07". X is maintained on a sheet called "Inputs". So in all, X,Y, Z and A,B,C are all copied for each scenario.
Visually Inspect The Data And Then Delete Any Column Containing Irregular Data
I have 1000's of columns of data to go through, I just need to visually inspect the data and then delete any column containing irregular data. At the moment; I select a section of the first column of data and insert a column chart for that data. I then need to change the source of the chart data to that of the next column, to do this I click on the data within the chart and it will show the data on the worksheet that I have selected by surrounding it in a blue box, I then click on this box and drag it to the next column of data. Clicking on the blue box (which sometimes I miss, and I then have to go back to the chart to activate it again) and dragging it to the next column is getting annoying, especially as I have sooooo much data to go through. Does anyone know if there is a shortcut key for this, or how I could write a macro to do this?
Lookup Or Data Change Based Upon The Data Reported In Column
I need to pull info to additional tabs. The problem I am having is that the data is sorted daily based upon performance numbers, so the REP names in column A can change based upon the data reported in column C.
What I want to accomplish:
Pull daily data for each unique name in column A to a team tab/worksheet that will be used to report that team's daily data.
Below is a representation of the data I need to break down by Rep Name and Team.
Rep DATA DATA DATA DATA DATA DATA DATA
1 2 3 4 5 6 7 REP 2 27.5% 14
51 86 REP 1 33.3% 14
42 74 REP 3 19.3% 11
57 86 REP 5 19.6% 9
46 66 REP 4 33.3% 9 ...........................
Copy Data From Another Workbook And Paste The Data From According To The Column Title
I have a time card report which will record the time spent on work for each employee. Each month, i have to generate the time card report and copy the data to my master file. Is there any code that can auto copy the time card entry directly to the master file? I only need some of the entry on the time card report, some of the column can be ignored.
I was thinking of creating a macro that will prompt me to choose the file to import as there are different file for different individual. Attach here with the master list (Demo.xls) & the time card for one individual (Nov-KTTHAM.xls) for reference.
I want to copy the data on column B of the time card to the column A of the master list, column C to column B, column D to column C, column E to column D, column F to column E and column G to column G.
Create Charts From Same Data But Each Based Ob Data Criteria
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
Convert Column Of Mixed Data Into Unique Data
I need to convert a column of data which will have repeated values into a column that turns that data into data with all unique values. Below is an example.
I had a solution until I wanted to turn this range into a list. The solution was.
When this data is in the list the <A2> in the range stops incrimenting from row to row and simply turns into the last row of the list. I have an example spread sheet upon request.