Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Results in Excel & VBA
Hi
I'm writing a reporting system that takes an array of data with 25 columns, and has a front end that allows the user to produce reports based on the required criteria. All is working fine, unless the value to search for strToFind is in multiple columns (for example, columns 19 (Success), 20 (Impact) & 21 (Effort) all have High,Medium & Low as options). For these searches, the report returns any rows which feature any of these values. How can I limit the search to only a specific column but return the entire array? Code is copied below - I have used a series of subroutines to get the strToFind, then pass it to a generic engine which runs the search and calls the formatting engine. This may be where the problem is, since searching a specific column may require passing the index of that column to the reporting engine. The variable strToFind is passed globally, it is working fine ---------------------------------------------------------------------------------------- Sub Success_Report() ' Success_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D37") Application.Run "'Report Generator v1.xls'!Run_Report" End Su ---------------------------------------------------------------------------------------- Sub Impact_Report() ' Impact_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D39") Application.Run "'Report Generator v1.xls'!Run_Report" End Su ---------------------------------------------------------------------------------------- Sub Effort_Report() ' Effort_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D41") Application.Run "'Report Generator v1.xls'!Run_Report" End Su ---------------------------------------------------------------------------------------- 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 Application.Run "'Report Generator v1.xls'!Clear_Report" Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Application.Run "'Report Generator v1.xls'!Select_Data" 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 v1.xls'!Report_Format" End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Results in Excel & VBA
you limit your search to
With Selection Find searches the selection. Make sure the column you want to search is selected. -- Regards, Tom Ogilvy "D Zandveld" wrote in message ... Hi I'm writing a reporting system that takes an array of data with 25 columns, and has a front end that allows the user to produce reports based on the required criteria. All is working fine, unless the value to search for strToFind is in multiple columns (for example, columns 19 (Success), 20 (Impact) & 21 (Effort) all have High,Medium & Low as options). For these searches, the report returns any rows which feature any of these values. How can I limit the search to only a specific column but return the entire array? Code is copied below - I have used a series of subroutines to get the strToFind, then pass it to a generic engine which runs the search and calls the formatting engine. This may be where the problem is, since searching a specific column may require passing the index of that column to the reporting engine. The variable strToFind is passed globally, it is working fine. ---------------------------------------------------------------------------------------- Sub Success_Report() ' Success_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D37") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- Sub Impact_Report() ' Impact_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D39") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- Sub Effort_Report() ' Effort_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D41") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- 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 Application.Run "'Report Generator v1.xls'!Clear_Report" Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Application.Run "'Report Generator v1.xls'!Select_Data" 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 v1.xls'!Report_Format" End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filtering Results in Excel & VBA
Thanks Tom, worked a treat.
"Tom Ogilvy" wrote: you limit your search to With Selection Find searches the selection. Make sure the column you want to search is selected. -- Regards, Tom Ogilvy "D Zandveld" wrote in message ... Hi I'm writing a reporting system that takes an array of data with 25 columns, and has a front end that allows the user to produce reports based on the required criteria. All is working fine, unless the value to search for strToFind is in multiple columns (for example, columns 19 (Success), 20 (Impact) & 21 (Effort) all have High,Medium & Low as options). For these searches, the report returns any rows which feature any of these values. How can I limit the search to only a specific column but return the entire array? Code is copied below - I have used a series of subroutines to get the strToFind, then pass it to a generic engine which runs the search and calls the formatting engine. This may be where the problem is, since searching a specific column may require passing the index of that column to the reporting engine. The variable strToFind is passed globally, it is working fine. ---------------------------------------------------------------------------------------- Sub Success_Report() ' Success_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D37") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- Sub Impact_Report() ' Impact_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D39") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- Sub Effort_Report() ' Effort_Report Macro recorded 20/02/2007 by (Me) strToFind = Worksheets("Main").Range("D41") Application.Run "'Report Generator v1.xls'!Run_Report" End Sub ---------------------------------------------------------------------------------------- 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 Application.Run "'Report Generator v1.xls'!Clear_Report" Application.ScreenUpdating = True Worksheets("Imported Data").Visible = True Worksheets("Imported Data").Activate Application.Run "'Report Generator v1.xls'!Select_Data" 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 v1.xls'!Report_Format" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting no results from Advanced Filtering in Excel 2007 | Excel Discussion (Misc queries) | |||
Filtering results | Excel Discussion (Misc queries) | |||
display count/results of filtering Excel in status bar | Excel Discussion (Misc queries) | |||
Filtering results | Excel Programming | |||
Filtering results | Excel Programming |