I would just use a data validation rule.
1. Select the range that needs to not allow duplicate entry.
2. Data/Data Tools/Data Validation on the ribbon.
3. For Validation Criteria:
ALLOW: Custom
FORMULA: =COUNTIF(A$2:A$1000,$A2)=1
(In the formula, A$2:A$1000 should be a range with the same rows you
highlighted for validation, but just the first column, and $A2 should be the
top left cell of the range).
IGNORE BLANK: whatever you want
4. Optionally change settings in the Error Alert tab. Note depending on the
alert type, Excel will either prevent duplicate entry, warn users with the
default to cancel their entry, or only warn users.
Excel Help mentions this method at
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010342173&NS
=EXCEL#BM4_8
Scroll up to the top of this page (the given link is bookmarked to near the
end) and read some of the other infromation for limitations to this data
validation method.
In my example I placed the $ signs in the range/cell references such that
the same formula will work/automatically change for each cell in even a
range including multiple columns and validate for no duplicates in each
column seperately.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Sam Mathai Chacko
Sent: Monday, October 03, 2011 12:16 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Help, Alert MsgBox Pair duplicate entry in row
Chil,
Paste this code in the sheet code module of the respective sheet. Now once
any entry is made in any row of B & C, it will check and display a message
if any duplicate is found.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngCount As Long
Dim lngLoop As Long
Dim strMsg As String
lngCount =
Evaluate("=SUMPRODUCT((Hoja1!$B$2:$B$57&Hoja1!$C$2:$C$57<>"""")*(Hoja1!$B$2:
$B$57=Hoja1!B" & Target.Cells(1).Row & ")*(Hoja1!$C$2:$C$57=Hoja1!C" &
Target.Cells(1).Row & "))")
If lngCount > 1 Then
strMsg = "Pair duplicated " & lngCount & " times in " &
Application.Rept(vbNewLine, 2)
For lngCount = lngCount To 1 Step -1
strMsg = strMsg & "Row " &
Evaluate("=SUMPRODUCT(LARGE((ROW(Hoja1!$B$2:$B$57))*(Hoja1!$B$2:$B$57&Hoja1!
$C$2:$C$57<>"""")*(Hoja1!$B$2:$B$57=Hoja1!B" & Target.Cells(1).Row &
")*(Hoja1!$C$2:$C$57=Hoja1!C" & Target.Cells(1).Row & ")," & lngCount &
"))") & vbNewLine
Next lngCount
MsgBox strMsg, vbOKOnly + vbInformation, "Duplicates"
End If
End Sub
Regards,
Sam
On Mon, Oct 3, 2011 at 10:15 PM, ChilExcel <chilexcel...@gmail.com> wrote:
hi
i Need VB lines codes for
Alert MsgBox Pair duplicate entry in row
Column D function account and duplicates alerts (NO Problem!)
Attach File , thank you all
--
Visita ; http://sites.google.com/site/chilexcel/Home
Visita ; http://www.youtube.com/user/timextag41
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
Sam Mathai Chacko
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
