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
|