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

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

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