Vba query

smartbuilder2k

New Member
Joined
Jan 18, 2005
Messages
44
Hello,


Question: How to avoid duplication in excel workbook.Suppose if the name of the company is (ABC Inc.)entered in one sheet, and while entering the same company name(i.e. ABC Inc.) in other sheet, the excel should show a message that the company name already exist in so and so sheet name.

Pls provide me the detailed answer along with the required tools to solve the above problem in vba coding or any of the way.
Waiting for your favourably reply

Regards
[/code]
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Welcome.

1. Press Alt-F11 to bring up VBA.
2. In the left pane, double-click on the sheet you want to put this in
3. In the right pane, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 And Target.Row > 1 Then
LookupName = ActiveCell.Text
Sheets("Sheet1").Select
On Error GoTo Message
Cells.Find(What:=LookupName, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
).Activate
Sheets("Sheet2").Select
Exit Sub
Message:
MsgBox ("Duplicate Name")
Sheets("Sheet2").Select
End If
End Sub

I hope that helps.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
The first thing I would do is take a moment to consider what range of cells among the sheets is likely to have data entered in it. On some sheets it might be A1:A10. On other sheets it might be B5:B20. The idea is, instead of evaluating all 16+ million cells on all sheets, only evaluate the range among all the sheets that would encompass the possible range on each sheet. That way, the workbook does not slow down to a crawl while the entire sheets are needlessly searched.

Let's say you determine that no sheet will have anything entered in it outside of the range A1:B20 (modify for whatever that range turns out to be for you).


Place this in your workbook module and see if it accomplishes what you are after. To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim ws As Worksheet, EvalRange As Range
Set EvalRange = Range("A1:B20")
If Intersect(Target, EvalRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub

'First, evaluate the parent sheet
If WorksheetFunction.CountIf(EvalRange, Target.Value) > 1 Then
MsgBox Target.Value & " already exists on this sheet."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If

'Next, evaluate the other workbook sheets
For Each ws In Worksheets
With ws
If .Name <> Target.Parent.Name Then
If WorksheetFunction.CountIf(Sheets(.Name).Range("A1:B20"), Target.Value) > 0 Then
MsgBox Target.Value & " already exists on the sheet named " & .Name & ".", _
16, "No duplicates allowed in " & EvalRange.Address(0, 0) & "."
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit For
End If
End If
End With
Next ws

End Sub

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Come to think of it, Find would probably be quicker than CountIf, but first let's see if this gets you closer to what you are trying to achieve, and it can be adjusted for speed later if need be.
 

smartbuilder2k

New Member
Joined
Jan 18, 2005
Messages
44
Hi all

Thanks for providing valuable suggestions in the form of coding.
The code provided by Mr. tactps and Mr. tom is really helpful for me on various levels of requirements.
I would like to thanks to all who supported me lot to solve the problem.

Thanks & Regards
Smart
 

Forum statistics

Threads
1,147,686
Messages
5,742,623
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top