Reversing a filter using a checkbox

[D Zandveld]

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

 

[Bob Phillips]

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)

 

[Tom Ogilvy]

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.