checking for dupes
 

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

checking for dupes
 

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" wrote in message
...
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

checking for dupes
 

On Jan 7, 1:50*pm, "Otto Moehrbach"
wrote:
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" wrote in message

...

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

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?

checking for dupes
 

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
"SangelNet" wrote in message
...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:
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" wrote in message

...

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

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?

checking for dupes
 

On Jan 7, 9:03*pm, "Otto Moehrbach"
wrote:
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"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



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" wrote in message

....

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

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

checking for dupes
 

On Jan 7, 9:03*pm, "Otto Moehrbach"
wrote:
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"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



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" wrote in message

....

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

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

checking for dupes
 

That line should all be on one line in VBA. Otto
"SangelNet" wrote in message
...
On Jan 7, 9:03 pm, "Otto Moehrbach"
wrote:
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"SangelNet" wrote in message

...
On Jan 7, 1:50 pm, "Otto Moehrbach"
wrote:



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" wrote in message

...

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

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

checking for dupes
 

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/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47606

checking for dupes
 

On Jan 16, 5:53*pm, Leith Ross
wrote:
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/)
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=47606

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.

checking for dupes
 

On Jan 19, 11:54*am, SangelNet wrote:
On Jan 16, 5:53*pm, Leith Ross
wrote:



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/)
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=47606

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

checking for dupes
 

SangelNet;188873 Wrote:
On Jan 19, 11:54*am, SangelNet wrote:
On Jan 16, 5:53*pm, Leith Ross
wrote:



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' ('The Code Cage' (http://www.thecodecage.com/))

------------------------------------------------------------------------
Leith Ross's Profile:'The Code Cage Forums - View Profile: Leith
Ross' (http://www.thecodecage.com/forumz/me...eith-ross.html)
View this thread:'checking for dupes - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=47606)

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

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/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47606

checking for dupes
 

On Jan 19, 4:12*pm, Leith Ross
wrote:
SangelNet;188873 Wrote:



On Jan 19, 11:54*am, SangelNet wrote:
On Jan 16, 5:53*pm, Leith Ross
wrote:

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' ('The Code Cage' (http://www.thecodecage.com/))

------------------------------------------------------------------------
Leith Ross's Profile:'The Code Cage Forums - View Profile: Leith
Ross' (http://www.thecodecage.com/forumz/me...eith-ross.html)
View this thread:'checking for dupes - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=47606)

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

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/)
------------------------------------------------------------------------
Leith Ross's Profile:http://www.thecodecage.com/forumz/member.php?userid=75
View this thread:http://www.thecodecage.com/forumz/sh...ad.php?t=47606

i have names like:

salesman 1
technician 2
administrator 4

in other word i have renamed some of the sheets.