MCSDPhil

Hi there,
I was interested by this problem as the code was quite tricky to follow.
I felt that I wanted to make it easier to follow, easier to debug and
add new conditions etc. I also felt that many parts of the code were
running repeatedly, making it a bit inefficient. So after some thought I
came up with this. I hope that I have understood the problem correctly,
and this helps.
Sub GetAll()
' Keyboard Shortcut: Ctrl+a
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim strCol1 As String
Dim strCol2 As String
Dim iRet As Integer
Dim dteEarliestDate As Date
Dim blnRun(5) As Boolean
Dim blnResult As Boolean

Sheets("Search").Select
Range("A9:L65536").Select
Selection.ClearContents
Application.Goto Reference:="R9C1"

Application.ScreenUpdating = False

lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row +
1

dteEarliestDate = Date - 90

WhichConditionsToRun blnRun

Select Case UCase(Sheets("Search").Range("C2"))
Case "FIRST", ""
strCol1 = "I"
strCol2 = "J"

Case "SECOND"
strCol1 = "L"
strCol2 = "M"

Case "FINAL"
strCol1 = "N"
strCol2 = "O"

Case Else 'the default/error option
'strCol1 = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select

For lngRow = 1 To lngLastRow
'Set to true to start with so we will only skip if a condition
returns False
blnResult = True
'Run Conditions
If blnRun(1) Then blnResult = Condition1(lngRow,
dteEarliestDate)
If blnResult And blnRun(2) Then blnResult = Condition2(lngRow)
If blnResult And blnRun(3) Then blnResult = Condition3(lngRow)
If blnResult And blnRun(4) Then blnResult = Condition4(lngRow,
strCol1)
If blnResult And blnRun(5) Then blnResult = Condition5(lngRow,
strCol2)

If blnResult Then
'All tests returned true so copy the line to the output page
Sheets(4).Range(lngNewRow & ":" & lngNewRow) =
Sheets("Details").Range(lngRow & ":" & lngRow)
lngNewRow = lngNewRow + 1
End If
Next

End Sub

Private Sub WhichConditionsToRun(ByRef blnRun() As Boolean)
'Check for which conditions should be run
With Sheets("Search")
If Len(.Range("E4").Value) 0 Then blnRun(1) = True
If Len(.Range("E2").Value) 0 Then blnRun(2) = True
If Len(.Range("E6").Value) 0 Then blnRun(3) = True
If Len(.Range("C4").Value) 0 Then blnRun(4) = True
If Len(.Range("C6").Value) 0 Then blnRun(5) = True
End With
End Sub

Private Function Condition1(ByVal lngRow As Long, ByVal dteEarliestDate
As Date) As Boolean
If Sheets("Details").Range("H" & lngRow) dteEarliestDate Then
Condition1 = True
End Function

Private Function Condition2(ByVal lngRow As Long) As Boolean
If Sheets("Details").Range("F" & lngRow) =
Sheets("Search").Range("E2") Then Condition2 = True
End Function

Private Function Condition3(ByVal lngRow As Long) As Boolean
If Sheets("Details").Range("E" & lngRow) =
Sheets("Search").Range("E6") Then Condition3 = True
End Function

Private Function Condition4(ByVal lngRow As Long, ByVal strCol1 As
String) As Boolean
If Sheets("Details").Range(strCol1 & lngRow) =
Sheets("Search").Range("C4") Then Condition4 = True
End Function

Private Function Condition5(ByVal lngRow As Long, ByVal strCol2 As
String) As Boolean
If Sheets("Details").Range(strCol1 & lngRow) =
Sheets("Search").Range("C6") Then Condition4 = True
End Function

Regards, Phil.

*** Sent via Developersdex http://www.developersdex.com ***