SangelNet
1/16/2009 5:42:00 PM
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-8366590315c2@e10g2000vbe.googlegroups.com...
> 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-eb90a0fb8fb1@i24g2000prf.googlegroups.com...
>
> > > 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