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

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" <(E-Mail Removed)> wrote in message
news:99178f7a-2369-477c-b1aa-(E-Mail Removed)...
> 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

On Jan 7, 1:50*pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <upslavazq...@gmail.com> wrote in message
>
> news:99178f7a-2369-477c-b1aa-(E-Mail Removed)...
>
> > 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?

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" <(E-Mail Removed)> wrote in message
news:027a2d11-154a-4e87-a427-(E-Mail Removed)...
On Jan 7, 1:50 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <upslavazq...@gmail.com> wrote in message
>
> news:99178f7a-2369-477c-b1aa-(E-Mail Removed)...
>
> > 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?

On Jan 7, 9:03*pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <upslavazq...@gmail.com> wrote in message
>
> news:027a2d11-154a-4e87-a427-(E-Mail Removed)...
> On Jan 7, 1:50 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> 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" <upslavazq...@gmail.com> wrote in message
>
> >news:99178f7a-2369-477c-b1aa-(E-Mail Removed)....
>
> > > 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

On Jan 7, 9:03*pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <upslavazq...@gmail.com> wrote in message
>
> news:027a2d11-154a-4e87-a427-(E-Mail Removed)...
> On Jan 7, 1:50 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> 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" <upslavazq...@gmail.com> wrote in message
>
> >news:99178f7a-2369-477c-b1aa-(E-Mail Removed)....
>
> > > 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

That line should all be on one line in VBA. Otto
"SangelNet" <(E-Mail Removed)> wrote in message
news:1cb26a66-267d-48a1-ad02-(E-Mail Removed)...
On Jan 7, 9:03 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
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" <upslavazq...@gmail.com> wrote in message
>
> news:027a2d11-154a-4e87-a427-(E-Mail Removed)...
> On Jan 7, 1:50 pm, "Otto Moehrbach" <moehrbachoex...@bellsouth.net>
> 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" <upslavazq...@gmail.com> wrote in message
>
> >news:99178f7a-2369-477c-b1aa-(E-Mail Removed)...
>
> > > 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

On Jan 16, 5:53*pm, Leith Ross <Leith.Ross.3m4...@thecodecage.com>
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.

On Jan 19, 11:54*am, SangelNet <upslavazq...@gmail.com> wrote:
> On Jan 16, 5:53*pm, Leith Ross <Leith.Ross.3m4...@thecodecage.com>
> 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

On Jan 19, 4:12*pm, Leith Ross <Leith.Ross.3ma...@thecodecage.com>
wrote:
> SangelNet;188873 Wrote:
>
>
>
> > On Jan 19, 11:54*am, SangelNet <upslavazq...@gmail.com> wrote:
> > > On Jan 16, 5:53*pm, Leith Ross <Leith.Ross.3m4...@thecodecage.com>
> > > 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.