checking for dupes

[SangelNet]

Hi guys,

in the intent to create a macro that checks for a dupe number with in
several sheets in a workbook(not all),

sheets 280 and 283 thru 288 i want to be able to be in column T and
enter a number if that number is already in column t of any of the
other sheets a msgbox will pop.

this is what i come up with.since im just strating to code its not
working. what is wrong here?

Sub dupcheck()
Dim c As Range
Dim ilast As Long

ilast = cell(Rows.Count, "t").End(xlUp).Row
For Each c In Workbook("sheet283:sheet288;sheet280").range("t4:T"
& ilast)
If Active.cell.Value = c.Range.Value Then
MsgBox ("message here")
End If

thnx

 

[Otto Moehrbach]

This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name <> Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) > 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If
End Sub

 

[SangelNet]

This macro will do what you want.  Read the code and ask questions as
needed.  This macro has to go into the ThisWorkbook module of your workbook.
HTH  Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim ws As Worksheet
    If (Sh.Name = "Sheet280" Or _
        Sh.Name = "Sheet283" Or _
        Sh.Name = "Sheet284" Or _
        Sh.Name = "Sheet285" Or _
        Sh.Name = "Sheet286" Or _
        Sh.Name = "Sheet287" Or _
        Sh.Name = "Sheet288") And _
        Target.Column = 20 Then
        If IsEmpty(Target.Value) Then Exit Sub
        For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
            If ws.Name <> Sh.Name Then
                With ws
                    If Application.CountIf(.Range("T:T"), Target.Value) > 0
Then
                        MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
                        Exit Sub
                    End If
                End With
            End If
        Next ws
    End If

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

 

[Otto Moehrbach]

In this context, the 20 is the number of the column, with Column A being the
1st column. Column T is the 20th column. The code at this point is saying
to take action only if the entry is in Column T and in one of the listed
sheets. HTH Otto

This macro will do what you want. Read the code and ask questions as
needed. This macro has to go into the ThisWorkbook module of your
workbook.
HTH Otto
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim ws As Worksheet
If (Sh.Name = "Sheet280" Or _
Sh.Name = "Sheet283" Or _
Sh.Name = "Sheet284" Or _
Sh.Name = "Sheet285" Or _
Sh.Name = "Sheet286" Or _
Sh.Name = "Sheet287" Or _
Sh.Name = "Sheet288") And _
Target.Column = 20 Then
If IsEmpty(Target.Value) Then Exit Sub
For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))
If ws.Name <> Sh.Name Then
With ws
If Application.CountIf(.Range("T:T"), Target.Value) > 0
Then
MsgBox "Duplicate entry found in sheet " & ws.Name &
".", 16, "Duplicate Found"
Exit Sub
End If
End With
End If
Next ws
End If

thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

 

[SangelNet]

In this context, the 20 is the number of the column, with Column A being the
1st column.  Column T is the 20th column.  The code at this point is saying
to take action only if the entry is in Column T and in one of the listed



thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx

 

[SangelNet]

In this context, the 20 is the number of the column, with Column A being the
1st column.  Column T is the 20th column.  The code at this point is saying
to take action only if the entry is in Column T and in one of the listed



thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx

 

[Otto Moehrbach]

That line should all be on one line in VBA. Otto

In this context, the 20 is the number of the column, with Column A being
the
1st column. Column T is the 20th column. The code at this point is saying
to take action only if the entry is in Column T and in one of the listed



thnx for the reply

im kinda lost in the part that says target.column = 20

the 20 represents or should represent the value or the colunm?

hi there, sorry about that ..I was out for a bit.

ran the code and got an error in this line:

For Each ws In Sheets(Array("Sheet280", "Sheet283", "Sheet284",
"Sheet285", "Sheet286", "Sheet287", "Sheet288"))

could it be because its refering to the sheets in sheets. I dont know
much, just speculating.

really interested in getting it to work.

thnx

 

[Leith Ross]

Hello SangelNet,

Provided the worksheet names are "Sheet280" etc., this code will take
check column "T" of sheet for duplications of whatever the active cell
contains.

==================================
Sub dupcheck()

Dim Dupe As Variant
Dim ID As Variant
Dim Wks As Worksheet

For Each ID In Array(280, 283, 284, 285, 286, 287)
Set Wks = Worksheets("Sheet" & ID)
Dupe = WorksheetFunction.CountIf(Wks.Columns("A"),
ActiveCell.Value)
If Dupe > 0 Then
MsgBox "Value has already been entered."
End If
Next ID

End Sub
==================================


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

 

[SangelNet]

Hello SangelNet,

Provided the worksheet names are "Sheet280" etc., this code will take
check column "T" of sheet for duplications of whatever the active cell
contains.

==================================
Sub dupcheck()

Dim Dupe As Variant
Dim ID As Variant
Dim Wks As Worksheet

For Each ID In Array(280, 283, 284, 285, 286, 287)
Set Wks = Worksheets("Sheet" & ID)
Dupe = WorksheetFunction.CountIf(Wks.Columns("A"),
ActiveCell.Value)
If Dupe > 0 Then
MsgBox "Value has already been entered."
End If
Next ID

End Sub
==================================

--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

I ran the code. placed it under "this workbook". changed the
corresponding letter for the column, but its not giving me an error
nor a result message.
played around with it for a bit and still, nothing.

 

[SangelNet]

I ran the code. placed it under "this workbook". changed the
corresponding letter for the column,  but its not giving me an error
nor a result message.
played around with it for a bit and still, nothing.

when i run the macro im getting a :
Application Defined Or Object-defined Error

 

[Leith Ross]

when i run the macro im getting a :
Application Defined Or Object-defined Error

Hello SangelNet,

Can you provide samples of your worksheet names? The error is probabl
due to not having the correct names in the macro

--
Leith Ros

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/

 

[SangelNet]

SangelNet;188873 Wrote:





Hello SangelNet,

Can you provide samples of your worksheet names? The error is probably
due to not having the correct names in the macro.

--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)

i have names like:

salesman 1
technician 2
administrator 4

in other word i have renamed some of the sheets.