help on duplication code

[maksinx]

hello everyone,

i have below code which works fine but when i write "?" on a cell either under column "i","o", or "n" gives me a warning which i dont want to be happen,

what i want to do is it should allow me write duplicate entries on row wise but should not allow me column wise.

can i also exclude "?" mark.The reason for this sometimes we dont know some information on column i,n,or o and we use "?" instead untill the information is available.
Please advice

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Dim IsDuplicate3 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("n:n"), Target.Value)
IsDuplicate3 = WF.CountIf(Columns("o:o"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2 + IsDuplicate3
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
Const WS_RANGE As String = "J:J" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[mdmackillop]

When you post code, select it and click the VBA button to format it as shown.

[mdmackillop]

Can you add in an If statement
If Not Target.Value = "?" Then
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)

[maksinx]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Dim IsDuplicate3 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("n:n"), Target.Value)
IsDuplicate3 = WF.CountIf(Columns("o:o"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2 + IsDuplicate3
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
Const WS_RANGE As String = "J:J" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[maksinx]

if you tell me where shall i paste i think i would do that,
thanks

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "J:J" '<== change to suit
Dim IsDuplicate As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Target.Value <> "?" Then

IsDuplicate = 0
Set WF = WorksheetFunction

If Not Intersect(Target, Me.Columns("I:I")) Is Nothing Then
IsDuplicate = WF.CountIf(Me.Columns("I:I"), Target.Value)
ElseIf Not Intersect(Target, Me.Columns("N:N")) Is Nothing Then
IsDuplicate = WF.CountIf(Me.Columns("N:N"), Target.Value)
ElseIf Not Intersect(Target, Me.Columns("O:O")) Is Nothing Then
IsDuplicate = WF.CountIf(Me.Columns("O:O"), Target.Value)
ElseIf Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
GoTo ws_exit
End If
End If
End With
End If

If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo

End If

ws_exit:
Application.EnableEvents = True
End Sub

[maksinx]

i didnt know the if statement but somehow i paste the code you given as below looks like it is working fine,


xld,
i will also test your code.

thanks millions


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim IsDuplicate As Integer
Dim IsDuplicate1 As Integer
Dim IsDuplicate2 As Integer
Dim IsDuplicate3 As Integer
Set WF = WorksheetFunction
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)
IsDuplicate2 = WF.CountIf(Columns("n:n"), Target.Value)
IsDuplicate3 = WF.CountIf(Columns("o:o"), Target.Value)
IsDuplicate = IsDuplicate1 + IsDuplicate2 + IsDuplicate3
If Not Target.Value = "?" Then
IsDuplicate1 = WF.CountIf(Columns("i:i"), Target.Value)
If IsDuplicate > 1 Then MsgBox "DUPLICATE!", vbCritical: Application.Undo
End If
Const WS_RANGE As String = "J:J" '<== change to suit
On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Value = UCase(.Value)
If .Value = "EXSA02" Or .Value = "EXSA03" Then
If .Offset(0, 3).Value = "" Then
MsgBox "EXSA SIP NUMBER CANNOT BE BLANK, PLS ENTER EXSA SIP NUMBER BEFORE CUSTOMER NAME"
.Value = ""
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[mdmackillop]

Thinking more, you just need to exit the sub, no need to check any further.
If Target.Value = "?" Then exit sub

[Bob Phillips]

Which is what mine does. Also, that code still isn't allowing duplicates across columns is it?

[maksinx]

xld,
your code works without any trouble,

you guys awesome... thanks alot...

I want to ask something else i have the attached file which consist of sheets called june 2007, color, and report. When i click on the command button (calculate without form method) on sheet june 2007 the code works and copies some data into sheet called report in that case some of them white color copied over and some of them blue.

Since at the actual file there are nearly 500 rows and 32 columns when the report works, i want to sort each colors individually by looking the values at column called ETA (column "E")

white rows should be sorted in ascending order in column e wise
blue rows also should be sorted in ascending order in column e wise
each colors should be sorted individually

Do you think it is possible to do that?

Please advice

Thanks in advance

[maksinx]

here is the file attached

regards

[mdmackillop]

each colors should be sorted individually
Can you explain this a bit more.

[maksinx]

yeah sure,

when i click on the command button macro gets data from jun 2007 and paste into report sheet,

it pastes 2 different type of date
1. the white ones
which is within in a date period, 5days from at present time basically it past all the white rows which are 5 days near to at present date

2. the blue ones
it paste the rows which has not got invoice number entered,the reason for that to show how many invoice and which ones should be invoiced.

macro also says x amount of eta process needs to be chased and x amount of he invoice to be issued,

since there are so many rows i have to put all the reported data in order to deal according to their priority...

white ones needs to be sorted by eta column / in ascending order because i need to start to deal with the one which has got the earliest time.

same goes for blue ones.

i hope this makes clear if not i can explain more.

[mdmackillop]

It looks as though you may have blocks of white and blue alternately. Is white (blue) to be sorted from top to bottom or within each block. If the former, can the sheet be sorted to leave all white/blue rows together.

[maksinx]

white and blue needs to be sorted seperately

in example

at the top the nearest time as below;
05/03/07
11/03/07
17/03/07