Modified vb - but it won't work

Greg · 12-21-2005, 10:30 AM

I am trying to modifiy an existing routine but can't get it to work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim l As Long

If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$16" Then
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Sheets("Lists").Range("$A$1").Value Then
Sheets("Lists").Range("$C$2").Value = ""
Else
Sheets("Lists").Range("$C$2").Value = Target.Value
End If

Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
End If

I'm using this in another workbook. The target address is "B2" on worksheet
"code activity". "B1:B2" is my range and the filter column begins at "A10".
Isn't it as simple as substituting this out? I'm guessing my problem is
this new workbook has everything on one worksheet and the original workbook
is checking two worksheets to get the info. I have this booogered up! Any
help?
Thanks,
Greg

Bob Phillips · 12-21-2005, 11:10 AM

This worked for me

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim l As Long

If Target.Count > 1 Then Exit Sub
If Target.Address = "$C$16" Then
If Me.FilterMode Then
Me.ShowAllData
End If
r = Cells(Rows.Count, 1).End(xlUp).Row
If Target.Value = Range("$A$1").Value Then
Range("$C$2").Value = ""
Else
Range("$C$2").Value = Target.Value
End If

Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
criteriaRange:=Range("$C$1:$C$2"), Unique:=False
End If
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Greg" <[email protected]> wrote in message
news:[email protected]...
> I am trying to modifiy an existing routine but can't get it to work.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim r As Long
> Dim l As Long
>
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$C$16" Then
> If ActiveSheet.FilterMode Then
> ActiveSheet.ShowAllData
> End If
> r = Cells(Rows.Count, 1).End(xlUp).Row
> If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> Sheets("Lists").Range("$C$2").Value = ""
> Else
> Sheets("Lists").Range("$C$2").Value = Target.Value
> End If
>
> Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> End If
>
> I'm using this in another workbook. The target address is "B2" on
worksheet
> "code activity". "B1:B2" is my range and the filter column begins at
"A10".
> Isn't it as simple as substituting this out? I'm guessing my problem is
> this new workbook has everything on one worksheet and the original
workbook
> is checking two worksheets to get the info. I have this booogered up!
Any
> help?
> Thanks,
> Greg

Greg · 12-21-2005, 02:30 PM

Bob,
I guess I'm not the brightest crayon in the box (great copier). I've spent
most the morning trying to get your routine to work also - no success. What
does 'me' mean? My worksheet says: Option Explicit and I do have another vb
on another worksheet. Could that be the problem?
Greg

"Bob Phillips" wrote:

> This worked for me
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim r As Long
> Dim l As Long
>
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$C$16" Then
> If Me.FilterMode Then
> Me.ShowAllData
> End If
> r = Cells(Rows.Count, 1).End(xlUp).Row
> If Target.Value = Range("$A$1").Value Then
> Range("$C$2").Value = ""
> Else
> Range("$C$2").Value = Target.Value
> End If
>
> Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> criteriaRange:=Range("$C$1:$C$2"), Unique:=False
> End If
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Greg" <[email protected]> wrote in message
> news:[email protected]...
> > I am trying to modifiy an existing routine but can't get it to work.
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim r As Long
> > Dim l As Long
> >
> > If Target.Count > 1 Then Exit Sub
> > If Target.Address = "$C$16" Then
> > If ActiveSheet.FilterMode Then
> > ActiveSheet.ShowAllData
> > End If
> > r = Cells(Rows.Count, 1).End(xlUp).Row
> > If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> > Sheets("Lists").Range("$C$2").Value = ""
> > Else
> > Sheets("Lists").Range("$C$2").Value = Target.Value
> > End If
> >
> > Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> > criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> > End If
> >
> > I'm using this in another workbook. The target address is "B2" on
> worksheet
> > "code activity". "B1:B2" is my range and the filter column begins at
> "A10".
> > Isn't it as simple as substituting this out? I'm guessing my problem is
> > this new workbook has everything on one worksheet and the original
> workbook
> > is checking two worksheets to get the info. I have this booogered up!
> Any
> > help?
> > Thanks,
> > Greg
>
>
>

Bob Phillips · 12-21-2005, 03:15 PM

Shouldn't be Greg.

Me refers to the code container, in this case the worksheet that it is in.
Is your code in the worksheet module?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Greg" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> I guess I'm not the brightest crayon in the box (great copier). I've
spent
> most the morning trying to get your routine to work also - no success.
What
> does 'me' mean? My worksheet says: Option Explicit and I do have another
vb
> on another worksheet. Could that be the problem?
> Greg
>
> "Bob Phillips" wrote:
>
> > This worked for me
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim r As Long
> > Dim l As Long
> >
> > If Target.Count > 1 Then Exit Sub
> > If Target.Address = "$C$16" Then
> > If Me.FilterMode Then
> > Me.ShowAllData
> > End If
> > r = Cells(Rows.Count, 1).End(xlUp).Row
> > If Target.Value = Range("$A$1").Value Then
> > Range("$C$2").Value = ""
> > Else
> > Range("$C$2").Value = Target.Value
> > End If
> >
> > Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> > criteriaRange:=Range("$C$1:$C$2"), Unique:=False
> > End If
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from email address if mailing direct)
> >
> > "Greg" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am trying to modifiy an existing routine but can't get it to work.
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim r As Long
> > > Dim l As Long
> > >
> > > If Target.Count > 1 Then Exit Sub
> > > If Target.Address = "$C$16" Then
> > > If ActiveSheet.FilterMode Then
> > > ActiveSheet.ShowAllData
> > > End If
> > > r = Cells(Rows.Count, 1).End(xlUp).Row
> > > If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> > > Sheets("Lists").Range("$C$2").Value = ""
> > > Else
> > > Sheets("Lists").Range("$C$2").Value = Target.Value
> > > End If
> > >
> > > Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> > > criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> > > End If
> > >
> > > I'm using this in another workbook. The target address is "B2" on
> > worksheet
> > > "code activity". "B1:B2" is my range and the filter column begins at
> > "A10".
> > > Isn't it as simple as substituting this out? I'm guessing my problem
is
> > > this new workbook has everything on one worksheet and the original
> > workbook
> > > is checking two worksheets to get the info. I have this booogered up!
> > Any
> > > help?
> > > Thanks,
> > > Greg
> >
> >
> >

Greg · 12-21-2005, 04:55 PM

Bob,
It's under the worksheet 'view code'.
Greg

"Greg" wrote:

> I am trying to modifiy an existing routine but can't get it to work.
>
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim r As Long
> Dim l As Long
>
> If Target.Count > 1 Then Exit Sub
> If Target.Address = "$C$16" Then
> If ActiveSheet.FilterMode Then
> ActiveSheet.ShowAllData
> End If
> r = Cells(Rows.Count, 1).End(xlUp).Row
> If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> Sheets("Lists").Range("$C$2").Value = ""
> Else
> Sheets("Lists").Range("$C$2").Value = Target.Value
> End If
>
> Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> End If
>
> I'm using this in another workbook. The target address is "B2" on worksheet
> "code activity". "B1:B2" is my range and the filter column begins at "A10".
> Isn't it as simple as substituting this out? I'm guessing my problem is
> this new workbook has everything on one worksheet and the original workbook
> is checking two worksheets to get the info. I have this booogered up! Any
> help?
> Thanks,
> Greg

Bob Phillips · 12-22-2005, 08:30 AM

In what way does it not work?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Greg" <[email protected]> wrote in message
news:[email protected]...
> Bob,
> It's under the worksheet 'view code'.
> Greg
>
> "Greg" wrote:
>
> > I am trying to modifiy an existing routine but can't get it to work.
> >
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Dim r As Long
> > Dim l As Long
> >
> > If Target.Count > 1 Then Exit Sub
> > If Target.Address = "$C$16" Then
> > If ActiveSheet.FilterMode Then
> > ActiveSheet.ShowAllData
> > End If
> > r = Cells(Rows.Count, 1).End(xlUp).Row
> > If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> > Sheets("Lists").Range("$C$2").Value = ""
> > Else
> > Sheets("Lists").Range("$C$2").Value = Target.Value
> > End If
> >
> > Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> > criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> > End If
> >
> > I'm using this in another workbook. The target address is "B2" on
worksheet
> > "code activity". "B1:B2" is my range and the filter column begins at
"A10".
> > Isn't it as simple as substituting this out? I'm guessing my problem is
> > this new workbook has everything on one worksheet and the original
workbook
> > is checking two worksheets to get the info. I have this booogered up!
Any
> > help?
> > Thanks,
> > Greg

Greg · 12-22-2005, 08:45 PM

Bob,
The rows that do not match the criteria should be hidden, leaving only the
criteria range rows. Greg

"Bob Phillips" wrote:

> In what way does it not work?
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "Greg" <[email protected]> wrote in message
> news:[email protected]...
> > Bob,
> > It's under the worksheet 'view code'.
> > Greg
> >
> > "Greg" wrote:
> >
> > > I am trying to modifiy an existing routine but can't get it to work.
> > >
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > Dim r As Long
> > > Dim l As Long
> > >
> > > If Target.Count > 1 Then Exit Sub
> > > If Target.Address = "$C$16" Then
> > > If ActiveSheet.FilterMode Then
> > > ActiveSheet.ShowAllData
> > > End If
> > > r = Cells(Rows.Count, 1).End(xlUp).Row
> > > If Target.Value = Sheets("Lists").Range("$A$1").Value Then
> > > Sheets("Lists").Range("$C$2").Value = ""
> > > Else
> > > Sheets("Lists").Range("$C$2").Value = Target.Value
> > > End If
> > >
> > > Range("$a20:$P" & r).AdvancedFilter Action:=xlFilterInPlace, _
> > > criteriaRange:=Sheets("Lists").Range("$C$1:$C$2"), Unique:=False
> > > End If
> > >
> > > I'm using this in another workbook. The target address is "B2" on
> worksheet
> > > "code activity". "B1:B2" is my range and the filter column begins at
> "A10".
> > > Isn't it as simple as substituting this out? I'm guessing my problem is
> > > this new workbook has everything on one worksheet and the original
> workbook
> > > is checking two worksheets to get the info. I have this booogered up!
> Any
> > > help?
> > > Thanks,
> > > Greg
>
>
>