Create Comparison Code In VBA

Jan 5, 2010

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 9 Replies


ADVERTISEMENT

VBA Code For Column Data Comparison?

Mar 17, 2014

Excel macros. I'm trying to figure out a script that will look at alphanumeric numbers in column A (A2 --> for example) and compare it to the number directly below it and above it. If it matches either, do nothing, but if it is different, delete the entire row that the different number resides on.

An example of the data would be:

A

12A34A5
12A34A5
67F86F3 - when it encounters these rows I need to delete.
23G45G6
23G45G6

The sheet is a couple thousand lines in length, so I can't deal with it manually or even with sorts, due to the other data in the other columns.

View 9 Replies View Related

Create Macro Code With VBA Code

Feb 22, 2008

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 3 Replies View Related

Using VBA To Create VBA Code On The Fly

May 17, 2006

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 2 Replies View Related

Create Search Code In VBA?

Mar 6, 2014

Create a search engine in vba for a worksheet, a macro to be assigned to a button. The 'search engine' to that 'Find button' that I have in mind is on these parameters:

- search any result, complete or partial, non-case sensitive;
- the text to search is typed in B2;
- after the text to search is typed, it only works by pressing that 'Find button';
- it only searches column D in the current worksheet;
- tells the total number of matches found in C2;
- when that Find button is clicked, it goes automatically to the first result found and stops;
- if that Find button is clicked again then it goes to the next found match and stops, and so on;
- when it reaches the end of the document, it just restarts to search from the beginning;
- if there are no matches, it just makes a sound and does nothing; and types zero (0) in C2.

View 11 Replies View Related

How To Create A Unique Code

May 7, 2008

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 9 Replies View Related

Code To Create New Sheet Each Quarter

Apr 21, 2014

Macro to run every 3 months, copy the workbook its in, save as a new name, and then delete all of the tabs in the workbook except for the "Menu" tab?

However, I get errors with my code below.

[Code] .....

View 7 Replies View Related

VBA Code To Create New Tabs In A Worksheet?

Sep 5, 2013

I don't know VBA code but i am familiar enough to know that a code can be written to separate out information into different tabs on a worksheet.

For example, I have a standard worksheet with say 20 columns going across and 20,000 rows going down. At each "name" change in row 1, I would like a macro to copy into a new tab. Basically the end result will have a tab for each name and the corresponding columns that go with the name.

View 2 Replies View Related

Code To Create Customer Number

Dec 14, 2007

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 9 Replies View Related

Modify Code To Create Worksheets

Dec 12, 2008

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 9 Replies View Related

Create New File & Name Via Macro Code

Sep 1, 2006

I have been trying to create a macro that creates a new workbook and prompts the user to enter a new filename for it. I'm getting stuck with the code.

View 3 Replies View Related

Code To Check For And Create New Folder

Sep 13, 2006

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 3 Replies View Related

Create Event Procedure Via Code

Nov 15, 2006

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 9 Replies View Related

Create Chart Automatically Via Code

Dec 19, 2006

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 6 Replies View Related

Create Workbook With Macro Code Within

Jan 11, 2007

Right now I use a VBA-Code to create new worksheets in a workbook. my Question:
is it possible to attach code to the newly created Worksheet via vba-code??

View 3 Replies View Related

Code To Create Folder And Inside Subfolder

Mar 7, 2014

The code below is sending back an error. It's supposed to create a folder and a subfolder within it and if the folder already exists then a msg box should pop up saying that folder already exists.

[Code] ....

View 14 Replies View Related

Code To Create/modify/save Templates..

Dec 11, 2009

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 5 Replies View Related

How To Use Solver Add-in Then To Create Own Data Analysis Code

Nov 18, 2011

Is it easier to use the Solver Add-in than to create your own data analysis code? We are planning to design a scheduler for around 12 technicians in our contact center (doing phone support).

View 5 Replies View Related

VBA Code To Create Border Around Used Cells In Worksheet

Nov 20, 2013

I'm writing/recording a macro and would like some VBA code. I'm trying to make a border around all used cells in my sheet. When I use the macro there will be a variable number of used cells.

I'd like code that says:

If cell A2 has text then format cells A2:CL2 with an outline border Repeat for each row in the sheet until there there is no more text in column A.

Also, not sure if it matters, but I'm working on a mac.

View 9 Replies View Related

Macro Code To Create Line Graph

May 2, 2014

I have a macro code that will create line graph referring the data given in defined column A1-C4,

Code with Static column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$4")
ActiveChart.ChartType = xlLineStacked

I tried to modify the above code, so it will refer undefined/dynamic data column, but getting an error during execution "Run Time Error - 424:" "Object required"

Code with Dynamic column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ActiveChart.ChartType = xlLineStacked

View 1 Replies View Related

Parse HTML Code, Create A Table

Mar 13, 2008

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 9 Replies View Related

Code To Create Directories From Cell Values

Feb 26, 2009

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 9 Replies View Related

Create CommandButton With Code & Position It On Sheet

Aug 25, 2008

How can I create a commandbutton with code & position it.

View 4 Replies View Related

VBA Macro Code To Create Multiple Charts

May 9, 2009

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 2 Replies View Related

Create Dynamic Named Ranges With Code

Oct 13, 2007

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 9 Replies View Related

Create UserForm With Controls Via Macro Code

Apr 20, 2008

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 6 Replies View Related

Comparison Of The Two Lines?

Feb 19, 2014

I have two rows of data, each row also have similar data. I need to find the number of matches that the two lines For example :

7,7,K,A,8,7
K,6,7,7,A,9

To reach the conclusion I need to build six such functions:
=1*(COUNTIF($A$65:$F$65,A66)>=COUNTIF($A66:A66,A66))
=1*(COUNTIF($A$65:$F$65,B66)>=COUNTIF($A66:B66,B66))
=1*(COUNTIF($A$65:$F$65,C66)>=COUNTIF($A66:C66,C66))
=1*(COUNTIF($A$65:$F$65,D66)>=COUNTIF($A66:D66,D66))
=1*(COUNTIF($A$65:$F$65,E66)>=COUNTIF($A66:E66,E66))
=1*(COUNTIF($A$65:$F$65,F66)>=COUNTIF($A66:F66,F66))

The result is 4

Is there a single formula that will give the result ?

Another question, is there a formula that I can compare to the principle of one line against the 20 lines ?

View 8 Replies View Related

If, Then Comparison With Result

Jun 12, 2009

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 3 Replies View Related

Comparison Macro

Oct 24, 2008

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 14 Replies View Related

Comparison Of Two Spreadsheets

Mar 6, 2009

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 15 Replies View Related







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