Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank.
What I currently do manually is:
1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2.
2: Highlight the differences
3: Move onto the next employee ID
All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row).
As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Create Macro Code With VBA Code
I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!) The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code... The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work). The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments. 'this code calls the method which creates the buttons in specific cells with specific names Private Sub CommandButton3_Click() 'the first call always works! AddCmdbuttonWithCode "Edit_111_111_114", 23 'the second one crashes excel AddCmdbuttonWithCode "Edit_111_111_115", 27 End Sub
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
VBA Macro Code To Create Multiple Charts
I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want: Sub Macro5() ActiveSheet.Shapes.AddChart.Select ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80" ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80" ActiveChart.ChartType = xlXYScatterSmoothNoMarkers ActiveWindow.SmallScroll Down:=-3 End Sub How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).
View Replies!
View Related
Delete Rows Via Comparison Using VBA
I have two tabs in a workbook. Each has a column containing a unique numerical identifier. What I want to do is create a macro that will loop through the column on tabA, verify it against the column on tabB and when a match occurs delete the row on tabA. The end result should be that only the new items on tabA will remain. Let me know if this is sufficiently clear or still a bit vague.
View Replies!
View Related
User Defined Date Comparison VBA
What I need is a Macro that will allow the user to enter a date (ex: 2/14/2008) and then look in column H for any dates prior to the user entered date and higlight the corresponding cell in Column A. I created the macro below from one in another workbook of mine and when I run it, I get a Run-Time error '13' Type Mismatch. When I go to Debug this code is highlighted in yellow: lookVal = InputBox("Securitization Date") How can this be fixed to accept a date entry in the Input Box? Sub SecuritzationDate() Application.ScreenUpdating = False Dim lookVal As Long lookVal = InputBox("Securitization Date") For i = 2 To Range("H" & Rows.Count).End(xlUp).Row If Range("H" & i).Value < lookVal Then Range("A" & i).Interior.ColorIndex = 40 End If Next i Application.ScreenUpdating = True End Sub
View Replies!
View Related
VBA Macro Code Needed To Run The Code Contained Within A Cell
I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains. Cell B5, for example, would contain the text: Range("B13").Formula = "SUM(D12:D14)" I need a macro to "execute this code", as if it were in the macro itself. I have assigned the above to a variable, but am not sure how to execute it. EG. Dim the_calc the_calc = Range("B5").value Now, how do I run the_calc ?
View Replies!
View Related
How To Create A Unique Code
Using a formula, How can i get excel to create a custom unique number using the first three (or two) characters from another field and then adding four unique numbers to the characters. basically heres the info Field H2 contains the characters "ACS" so i would want another field to create a unique code to include something like "ACS1234" and to continue to the next row would be the first three characters of field H3 then 1235.
View Replies!
View Related
Create Event Procedure Via Code
I'm trying to add a worksheet event via code. It works OK when I run it on its own but I get a run-time error 9 subscript out of range error when I try to run it from another procedure. Please find attached my 2 bits of code. Any help greatly appreciated. This is the bit that works OK on its own but not when called from my other procedure Sub Code_To_Write_Code() 'This writes code to the new sheet Dim StartLine As Long Dim SheetToAddCodeTo SheetToAddCodeTo = ActiveSheet.CodeName With ActiveWorkbook.VBProject.VBComponents(SheetToAddCodeTo).CodeModule StartLine = .CreateEventProc("Calculate", "Worksheet") + 1 ..............
View Replies!
View Related
Modify Code To Create Worksheets
I have this code that will create 3 worksheets with the name "New Name" and the # ( 1/2/3 ) .. How would I change the code if I want 3 worksheets with different name? Like Sheet1 then Sheet 2 then Sheet3 or any other name? 'To add worksheets and change name with one code For i = 1 To 3 'Creates 3 worksheets Set ws = Worksheets.Add ws.Name = "New Name" & i Next i
View Replies!
View Related
Code To Create Customer Number
I have the following formula copied down from A3 to A200 in a spreadsheet, which creates a unique standardized customer number for the each names listed in B3 to B200: =IF(B3="","","S"&LEFT(B3,1)&"-"&TEXT(SUMPRODUCT(--(LEFT($B$3:B3,1)=LEFT(B3,1)))*10,"0000")) Therefore, with the following names listed in B3 to B10: UPS FedEx Fisher Price Bell Canada Grand & Toy Rogers Cable Dominion Blue Jays The respective customer numbers would be: SU-0010 SF-0010 SF-0020 SB-0010 SG-0010 SR-0010 SD-0010 SB-0020 I would like these numbers to be static, so I would appreciate it very much if someone can give me a VBA code to replace the formula.
View Replies!
View Related
Create Chart Automatically Via Code
to write a code that would create a chart automatically in another worksheet when a button is clicked. I have attached the sample data that I am working on. Each row in the sheet represent 3 coordinates, which are: coordinate # 1 = start, 0 coordinate # 2 = centre, level coordinate # 3 = end, 0
View Replies!
View Related
Code To Check For And Create New Folder
I have a macro that creates a spreadsheet on a weekly basis and have been using it for quite sometime. Due to the large number of spreadsheets I would like to incorporate some code that would create a folder every month. I would assume that the code would need to include a check routine to see if a folder for that month exsists.
View Replies!
View Related
Efficient VBA Code Vs Macro Recorder Code
What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code. 1) Macro Recorder Code to Copy One Sheet to Another Sheets("Data Apple").Select Cells.Select Selection.Copy Sheets("Banana").Select Cells.Select ActiveSheet.Paste 2) Macro Recorder Code for Replacement Purposes......................
View Replies!
View Related
Display All VBA Code In Code Window (VBE)
When I started using VBA, all the code for each module was shown in the code window. This was a bit cumbersome when looking for one sub in particular. Now when I load VBA, all the Subs and Functions have their own page, and can be selected from the right hand drop down list. This is much easier for entering code, and finding subs. However, it is sometimes useful to see 'the big picture', so how do I swap between the two types of display?
View Replies!
View Related
Parse HTML Code, Create A Table
I have a function that locates a table on a webpage and pulls the html code into one cell in a worksheet. Basically we can call this one cell a text file. I need to parse through this text file (cell A1) to create a table. This text file only has info for 1 table, the table always has 12 columns, the rows are variable. I would like it to then write back this parsed text file back into excel as a table, say starting in cell A2 on the same worksheet.
View Replies!
View Related
Code To Create Directories From Cell Values
I have a CSV file and would like to create directories on my hard drive and the names of those directories will need to be pulled out from this csv file: [B1:B86]. For example I have the following data in my csv file: B1: folder1 B2: folder2 . . . And would like to create folders on my hard drive so that folder names will be: folder1, folder2, ...
View Replies!
View Related
Code To Create/modify/save Templates..
I have quite a few Excel templates that need to be modified by changing/adding information or sheets to them. Instead of working through the lot manually, it would be good if I could do the same through code. With my little knowledge I can do this for workbooks. Need to typical code snippets or suggestions? e.g. to open a template file, save it with the original name, etc. The usual Dim srcWB as workbook, Set srcWB= ABC.xls and Workbooks(ABC.xls). open do not seem to work with template files (ABC.xlt)
View Replies!
View Related
Create Dynamic Named Ranges With Code
I am employing code to label dynamic ranges that takes the form of, ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))" 'dms_j ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)" 'dms_p ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)" 'dms_r ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)" 'dms_t ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)" The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.
View Replies!
View Related
Create UserForm With Controls Via Macro Code
This code create a userform on the workbook opening .I'm having problems with 1. Closeing the Visual basic window after this procedure runs 2. Being able to use the create userformstext box value in other procedures 3. how to delete the userform on close Sub AddUserFormInputRequest() Dim objVBProj As VBProject Dim objVBComp As VBComponent Dim objVBFrm As UserForm Dim objChkBox As Object Dim x As Integer Dim strCode As String Dim firstLine As Long, SecondLine As Long Set objVBProj = Application.VBE.ActiveVBProject Set objVBComp = objVBProj.VBComponents.Add(vbext_ct_MSForm) With objVBComp . Name = "InputRequest" . Properties("Width") = 200 .Properties("Height") = 100......................
View Replies!
View Related
VBA Code To INSERT CODE - GURUS
I have a worksheet named mylist, that I delete, then recreate everytime I run a specific macro. However, I have code in this worksheet that I require, so is there a way for me to insert code to "insert code" on the "re-creation" of the worksheet?
View Replies!
View Related
VBA Code- How Do You Get The Code Boxes To Appear In Threads
I have code that inserts columns, inserts formulas, and then copies the formulas to the last row of data. It all works good but for some reason, the columns that are being copied, are being copied past the last row of data. It can always be determined how far down it will be copied. Examples: if the last row of data was row 4, then the formulas would be copied down to row 24, if the last row of data was row 54, then the formulas would be copied down to 254, if the last row of data was row 284, then the formulas would be copied down to 2284. I can not figure out how and wh this is happening, but whatever the last row of actual data is on the spreadsheet, there is a 2 being placed in front of the last row of data and the formulas are being copied down to whatever the last row is with the addition of a 2 in front. Here is the code for the columns additions, and formulas: Columns("O:S").Insert Shift:=xlToRight Columns("W:Z").Insert Shift:=xlToRight Columns("AB:AF").Insert Shift:=xlToRight Columns("AH:Al").Insert Shift:=xlToRight Range("O2").FormulaR1C1 = "=CONCATENATE(RC[1],""/"",RC[2],""/"",RC[3])" Range("P2").FormulaR1C1 = "=IF((RC[3]=7),LEFT(RC[-2],1),LEFT(RC[-2],2))" Range("Q2").FormulaR1C1 = "=IF((RC[2]=7),MID(RC[-3],2,2),MID(RC[-3],3,2))" Range("R2").FormulaR1C1 = "=RIGHT(RC[-4],4)" Lets take this first one for example: Range("O2:S2").Copy Range("O2:S2", Range("O2:S2" & Range("A" & Rows.Count).End(xlUp).Row)) If the last row of data was row 54, then columns O:S would have the formulas copied down to row 254. How can this code be modified so the formulas will not be copied past the last row of data? How do you get the code boxes to appear in these threads. I do not know how to do this. As you can see, all I did was copy and paste my code in this thread.
View Replies!
View Related
Create A New Table That Displays The Information By Location Instead Of Code
I have a table of information with location codes as the column headers. Each location has from 1 to 6 codes associated with it. I want to create a new table that displays the information by location instead of code, i.e. adding all of the codes for a location into one column for that location. I'm hoping the example will make this clearer. On the main page, I am trying to add in the wa column all of the columns in the raw page that have a code associated with wa as the header. To make this more complex, I can't use vba on this one. the only thing I've got so far is a very long, very complex formula that adds together numbers generated from index/matching each entry in the second table. something like this. (edit changing 1:1 to $1:$1) =IF(VLOOKUP(B$1,lookup1,2,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,2,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,3,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,3,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,4,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,4,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,5,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,5,FALSE),raw!$1:$1,0)))+IF(VLOOKUP(B$1,lookup1,6,FALSE)=0,0,INDEX(sample.xls!main_table,ROW(),MATCH(VLOOKUP(B$1,lookup1,6,FALSE),raw!$1:$1,0))) It works, but its horribly ugly, and if the number of locations goes higher (we could be looking at going to ten location codes for one of our locations) then I'll actually exeed the 1000 characters per formula limit!
View Replies!
View Related
To Create A Userform With A Textbox To Enter A Product Code
i would like to create a userform with a textbox to enter a product code , a command button to run VBA and a big text box to show output of VBA from Textbox 1 which i will connect to DB and pull back information relevant to that product code , is this possible i can create the userform no problem and already have code to pull back from DB into Excel ,
View Replies!
View Related
Create Range List Code For Data Validation
I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code 1. The data of validation list is stored under the column heading 'Reported_By_List' 2. Validation is applied on the range under the column heading 'Reported Through' 3. Start and last Cell Address Of the cells of the column in which data of validation list is stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 ' 4. Start and last Cell Address Of the cells of the column on which data validation will be applied are stored are extracted into the variables 'StartCellAddress2' & 'LastCellAddres2' Sub Validation1() Dim wsName As String Dim RValue As String Dim WorkBookName As String Dim StartRow As Integer Dim StartColumn As Integer Dim LastRow As Integer Dim CellAddress As String Dim ColumnAddress As String Application. ScreenUpdating = False WorkBookName = ThisWorkbook.Name wsName = ActiveSheet.Name...........
View Replies!
View Related
Sort And Move Code, Create Equipment Lists`
I have been using Excel to create equipment lists for my work. The forum has helped me by creating a visual basic macro to create a 'final list' sheet compiled from the other source worksheets. On my 'final list' sheet I need the first 10 rows to be left out of the macro’s so that I can add header information like job reference etc. I have tried altering the visual basic code by adjusting the row numbers in it so that it starts at row 10 but this keep's on producing error codes. This is due to my limited knowledege of what each line does. I also wish to be able to paste new equipment into the final list sheet. When I do this at the moment the code does not recognise it is there and does not carry the information accross back into the source sheet. It is the same when I block select an area and delete the code does not recognise I have done this which is fustrating.
View Replies!
View Related
Create Multiple Buttons All Assigned To Macro Code
I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.
View Replies!
View Related
Comparison Of Two Spreadsheets
I have two spreadsheets, The spreadsheet #1 has the information of two years and the Spreadsheet #2 is a montly report. The Spreadsheet # 1 hasta on the column A a number of transaction, and at the column Q the invoice number. The Spreadsheet # 2, only has in the column A the Invoice number. I need to find in the Spreadsheet # 1 the Invoice numbers that match the Invoices that I have in the Spreadsheet # 2 by bringin the "Number of Transaction". For example: Spreadsheet 1: Has on column A3 the Number of Transaction # 0123, and at the column Q3 the Invoice number 555. At the Spreadsheet number # 2 I have the INvoice # 555 located on A10. I need to know what is the transaction # by adding a formula in a new column (G) so I want to have the Transaction # 0123 in the new column added G. If I do this manually it will take me hours since these report and the Master is so big, and right now I am doin it using Ctrl-F
View Replies!
View Related
Comparison Macro
to compare the values of data on one sheet (sheet1) with that of another (sheet2) to see if there has been any changes between the sheets over the previous week. If a macro could go through my data on sheet1, compare cell-by-cell the data that's in sheet 2 and highlight in red font any cells that have different values that would be awesome. The macro would need to leave the "fill color" of the cell as it was.
View Replies!
View Related
Date Comparison
How in VBA can you evaluate a cell in the "general" format and in the "date" format? I'd perfer do this entirely in VBA without use of a helper column in Excel.
View Replies!
View Related
Data Comparison
I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.
View Replies!
View Related
Comparison Of Lines
There are four lines of numbers, in all line 8 numbers. 1 1 2 2 3 1 1 2 1 2 3 1 2 2 2 1 2 1 1 3 1 3 1 2 1 2 2 2 3 1 1 2 I would like to find a function that will examine, if there are similar lines according to my definition, and several. my definition is 7 For example that I gave, the result in the wanted function needs to be, yes (Line 1 and line 4) and several-One time.
View Replies!
View Related
If, Then Comparison With Result
Compare 2 columns of data and have a new 3th column tell me the result of the query based on some conditional information. So, here's an example. Column A = Programmer Assigned (Yes/No) Column B = Project State (concept, plan, develop, qualify, rollout, etc) I'd like to compare the values in A to B and have the formula tell me if the programmer should be assigned in that phase or not. So, in english. If A = No, then look to see if B = "develop" or "qualify", if it does, populate C with "Update required". If it doesn't, populate with "Correct". I hope I explained it well enough for you all to help. I originally looked into using conditional formatting to just format the cell differently if I needed to perform an action.
View Replies!
View Related
Comparison (Strings)
I am trying to compare two cells to see if its two values are the same. Some of the cells have a space in between numbers or a symbol like "-" Ex. one cell's value is 1213180IN and the other cell's value is 1213180-IN the program I have written marks these as different because of the "-" in the second cell....is there a way, maybe with a string function to overlook that "-"?
View Replies!
View Related
Create Surface Chart Via Macro Code Based On Selection
I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them. Public Sub AddChart2(LastRow As Integer, LastCol As Integer) Dim cht As ChartObject, currentSheet As Worksheet Dim rng As Range, newRow As Range, srcRange As Range Dim colIdx As Integer colIdx = 5
View Replies!
View Related
Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default. the script is like this : ActiveSheet.ChartObjects("Chart 31").Activate Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
|
TRACKER
