| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modified vb - but it won't work
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
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" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
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" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
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" wrote in message ... 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" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
In what way does it not work?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Greg" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Modified vb - but it won't work
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" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| modified vlookup help | Excel Worksheet Functions | |||
| Last Modified by... | Excel Discussion (Misc queries) | |||
| Last Modified | Excel Worksheet Functions | |||
| <HELP modified reference | Excel Worksheet Functions | |||
| Modified Excel?????? | Excel Discussion (Misc queries) | |||
Linear Mode
