Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reversing a filter using a checkbox
Hi
I have this code working perfectly, which takes a variable (strToFind), searches an array (Import_Data), and outputs all the matching records. easy. There is also a function for reporting all values AllProjects_Report(). However, I want to include a checkbox labelled 'Exclude', which, if selected, excludes these records from AllProjects_Report (effectively producing a NOT report). Apples $1.50 Roger Apples $2.00 Ron Peach $1.75 Steve Banana $4.00 John At the moment, selecting Apples as your criteria would produce: Apples $1.50 Roger Apples $2.00 Ron But if the box is checked, i'd like it to produce; Peach $1.75 Steve Banana $4.00 John logically: If Exclude_Box = True, AllProjects_Report - Custom_Report Below is the code for running a report, which is where I think I need to alter. __________________________________________________ _______________ Sub Run_Report() ' Run_Report Macro recorded 20/02/2007 by (me) ' This macro is the reporting engine ' It is called by each macro after the report criteria is set Dim intS As Integer Dim rngY As Range Dim wSht As Worksheet intS = 1 'Clear any existing report information Worksheets("Report").Range("A:Z").Clear Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Set wSht = Worksheets("Report") With Selection Set rngY = .Find(what:=strToFind, LookAt:=xlPart) If Not rngY Is Nothing Then FirstAddress = rngY.Address Do rngY.EntireRow.Copy wSht.Cells(intS, 1) intS = intS + 1 Set rngY = .FindNext(rngY) Loop While Not rngY Is Nothing And rngY.Address < FirstAddress End If End With ' Run the report formatting engine Worksheets("Imported Data").Visible = False Application.Run "Report Generator v2.xls'!Report_Format" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reversing a filter using a checkbox
Sub Run_Report()
' Run_Report Macro recorded 20/02/2007 by (me) ' This macro is the reporting engine ' It is called by each macro after the report criteria is set Dim FirstAddress As String Dim intS As Integer Dim rngY As Range Dim wSht As Worksheet Dim nDirection As Long Dim StartCell As Range Dim cbDrive As CheckBox Application.ScreenUpdating = True Set wSht = Worksheets("Report") 'Clear any existing report information wSht.Range("A:Z").Clear With Worksheets("Imported Data") .Visible = True .Activate End With With Selection Set cbDrive = wSht.CheckBoxes("Check Box 1") intS = 1 If cbDrive.Value = 1 Then Set StartCell = Selection(Selection.Count) .Copy wSht.Range("A1") nDirection = xlPrevious Else Set StartCell = .Cells(1, 1) nDirection = xlNext End If Set rngY = .Find(What:=strToFind, _ After:=StartCell, _ LookAt:=xlPart, _ SearchDirection:=nDirection) If Not rngY Is Nothing Then FirstAddress = rngY.Address Do If cbDrive.Value = 1 Then wSht.Rows(rngY.Row).Delete Else rngY.EntireRow.Copy wSht.Cells(intS, 1) End If intS = intS + 1 Set rngY = .FindNext(rngY) Loop While Not rngY Is Nothing And rngY.Address < FirstAddress End If End With ' Run the report formatting engine Worksheets("Imported Data").Visible = False Application.Run "Report Generator v2.xls'!Report_Format" End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "D Zandveld" wrote in message ... Hi I have this code working perfectly, which takes a variable (strToFind), searches an array (Import_Data), and outputs all the matching records. easy. There is also a function for reporting all values AllProjects_Report(). However, I want to include a checkbox labelled 'Exclude', which, if selected, excludes these records from AllProjects_Report (effectively producing a NOT report). Apples $1.50 Roger Apples $2.00 Ron Peach $1.75 Steve Banana $4.00 John At the moment, selecting Apples as your criteria would produce: Apples $1.50 Roger Apples $2.00 Ron But if the box is checked, i'd like it to produce; Peach $1.75 Steve Banana $4.00 John logically: If Exclude_Box = True, AllProjects_Report - Custom_Report Below is the code for running a report, which is where I think I need to alter. __________________________________________________ _______________ Sub Run_Report() ' Run_Report Macro recorded 20/02/2007 by (me) ' This macro is the reporting engine ' It is called by each macro after the report criteria is set Dim intS As Integer Dim rngY As Range Dim wSht As Worksheet intS = 1 'Clear any existing report information Worksheets("Report").Range("A:Z").Clear Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Set wSht = Worksheets("Report") With Selection Set rngY = .Find(what:=strToFind, LookAt:=xlPart) If Not rngY Is Nothing Then FirstAddress = rngY.Address Do rngY.EntireRow.Copy wSht.Cells(intS, 1) intS = intS + 1 Set rngY = .FindNext(rngY) Loop While Not rngY Is Nothing And rngY.Address < FirstAddress End If End With ' Run the report formatting engine Worksheets("Imported Data").Visible = False Application.Run "Report Generator v2.xls'!Report_Format" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reversing a filter using a checkbox
Might want to look at the Advanced filter under filters under the Data menu.
It should do all this without all the huffing and puffing. Just set up the criteria you want, including exluding records. for example to exclude apples Dummy =A4<"Apples" goes in the criteria range. A4 refers to the first row of data (in your example it is the containing Applels and Roger. If I just wanted apples it would be Fruit Apples (fruit would be the column header for your data This can all be done with a couple lines of code. -- Regards, Tom Ogilvy "D Zandveld" wrote: Hi I have this code working perfectly, which takes a variable (strToFind), searches an array (Import_Data), and outputs all the matching records. easy. There is also a function for reporting all values AllProjects_Report(). However, I want to include a checkbox labelled 'Exclude', which, if selected, excludes these records from AllProjects_Report (effectively producing a NOT report). Apples $1.50 Roger Apples $2.00 Ron Peach $1.75 Steve Banana $4.00 John At the moment, selecting Apples as your criteria would produce: Apples $1.50 Roger Apples $2.00 Ron But if the box is checked, i'd like it to produce; Peach $1.75 Steve Banana $4.00 John logically: If Exclude_Box = True, AllProjects_Report - Custom_Report Below is the code for running a report, which is where I think I need to alter. __________________________________________________ _______________ Sub Run_Report() ' Run_Report Macro recorded 20/02/2007 by (me) ' This macro is the reporting engine ' It is called by each macro after the report criteria is set Dim intS As Integer Dim rngY As Range Dim wSht As Worksheet intS = 1 'Clear any existing report information Worksheets("Report").Range("A:Z").Clear Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Set wSht = Worksheets("Report") With Selection Set rngY = .Find(what:=strToFind, LookAt:=xlPart) If Not rngY Is Nothing Then FirstAddress = rngY.Address Do rngY.EntireRow.Copy wSht.Cells(intS, 1) intS = intS + 1 Set rngY = .FindNext(rngY) Loop While Not rngY Is Nothing And rngY.Address < FirstAddress End If End With ' Run the report formatting engine Worksheets("Imported Data").Visible = False Application.Run "Report Generator v2.xls'!Report_Format" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to have Checkbox A uncheck with checked Checkbox B | Excel Discussion (Misc queries) | |||
link a checkbox in a sheet to a checkbox on a userform? | Excel Programming | |||
How do I link one checkbox to update another checkbox? | Excel Programming | |||
checkbox on form reset from checkbox on sheet | Excel Programming | |||
Can you filter on an ActiveX checkbox in Excel | Excel Discussion (Misc queries) |