I have 4 conditions

1) If Sheets("Details").Range("H" & lngRow) Date - 90 And _
2) Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _
3) Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _
4) Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then

if Sheets("Search").Range("E2") is blank then condition 1 should be skipped
if Sheets("Search").Range("C2") is blank then condition 2 should be skipped
if Sheets("Search").Range("C4") is blank then condition 3 should be skipped
if Sheets("Search").Range("E4") is blank then condition 4 should be skipped

Please help me with this..

--------------------------------------

Sub GetAll()
Dim lngRow As Long
Dim lngLastRow As Long
Dim lngNewRow As Long
Dim varTemp As Variant

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
For lngRow = 1 To lngLastRow

If Sheets("Details").Range("H" & lngRow) Date - 90 And _
Sheets("Details").Range("F" & lngRow) = Sheets("Search").Range("C2") And _
Sheets("Details").Range("J" & lngRow) = Sheets("Search").Range("C4") And _
Sheets("Details").Range("I" & lngRow) = Sheets("Search").Range("E4") Then

varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next

this might do it for you:


VBA Code:
--------------------


Application.ScreenUpdating = False
lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Search")
For lngRow = 1 To lngLastRow
If (Sheets("Details").Range("H" & lngRow) Date - 90 Or Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("C2") Or Len(.Range("C2").Value) = 0) And _
(Sheets("Details").Range("J" & lngRow) = .Range("C4") Or Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range("I" & lngRow) = .Range("E4") Or Len(.Range("E4").Value) = 0) Then
varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next
End With

--------------------


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202176

http://www.thecodecage.com/forumz

Thank you Pascal.. :)

is it also possible that we can change the lookup column according to the
value of the cell?

for eg., in the below statement

if the value of Range("C4") = "First" it should lookup in Range("J" & lngRow)

if the value of Range("C4") = "Sec" it should lookup in Range("M" & lngRow)

------------------------------------------

(Sheets("WFM Details").Range("J" & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _

untested:

VBA Code:
--------------------


Application.ScreenUpdating = False
lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Search")
Select Case UCase(.Range("C4"))
Case "SEC": TheCol = "M"
Case "FIRST", "": TheCol = "J"
Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C4 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow
If (Sheets("Details").Range("H" & lngRow) Date - 90 Or Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("C2") Or Len(.Range("C2").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range("I" & lngRow) = .Range("E4") Or Len(.Range("E4").Value) = 0) Then
varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next
End With
--------------------





Kash;722155 Wrote:

Thank you Pascal.. :)

is it also possible that we can change the lookup column according to
the
value of the cell?

for eg., in the below statement

if the value of Range("C4") = "First" it should lookup in Range("J" &
lngRow)

if the value of Range("C4") = "Sec" it should lookup in Range("M" &
lngRow)

------------------------------------------

(Sheets("WFM Details").Range("J" & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202176

http://www.thecodecage.com/forumz

Hello Pascal.. your code works fine..

I did some changes and added 2 more CASE conditions and it stopped working..
Would you please check and let me know what's going wrong?

my code below..
--------------------------------

Sheets("Search").Range("A10:L65536").Select
Selection.ClearContents

Application.ScreenUpdating = False
lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Search")
Select Case UCase(.Range("C2"))
Case "SECOND": TheCol = "L"
Case "FIRST", "": TheCol = "I"

Case "SECOND", "": TheColmn = "M"
Case "FIRST", "": TheColmn = "J"

Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow
If (Sheets("Details").Range("H" & lngRow) Date - 90 Or
Len(.Range("E4").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("E2") Or
Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range(TheColmn & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then
varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next
End With

In a *Case Select* statement, the first *Case *which satisfies gets
executed and code execution jumps to the line after *End Case*. So
1. there is no point in in having the same condition in two *Case
*statements
2. the order of the *Case *statements is important


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202176

http://www.thecodecage.com/forumz

So how should I go ahead now as I am planning for 2 Case

Case "SECOND": TheCol = "L"
Case "FIRST", "": TheCol = "I"

Case "SECOND", "": TheColmn = "M"
Case "FIRST", "": TheColmn = "J"

(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _

(Sheets("Details").Range(TheColmn & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then

VBA Code:
--------------------


Case "FIRST", "": TheCol = "I": TheColmn = "J"
Case "SECOND": TheCol = "L": TheColmn = "M"
--------------------




though I'm guessing that a blank cell is to be treated as if *First*
was there


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202176

http://www.thecodecage.com/forumz

Its giving me a run time error..

my code
-------------------

Case "FIRST", "": TheCol = "I": TheColmn = "J"
Case "SECOND": TheCol = "L": TheCol1 = "M"


Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow

If (Sheets("Details").Range("H" & lngRow) Date - 90 Or
Len(.Range("E4").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("E2") Or
Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range(TheCol1 & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then

typo: TheColmn is not used, should the first line be:
Case "FIRST", "": TheCol = "I": TheCol1 = "J"
?

Kash;723181 Wrote:

Its giving me a run time error..

my code
-------------------

Case "FIRST", "": TheCol = "I": TheColmn = "J"
Case "SECOND": TheCol = "L": TheCol1 = "M"


Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow

If (Sheets("Details").Range("H" & lngRow) Date - 90 Or
Len(.Range("E4").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("E2") Or
Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range(TheCol1 & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=202176

http://www.thecodecage.com/forumz

Oh ya.. I got it.. Thank you Pascal

Hello Pascal.. again its giving me some error.. Would u please check this for
me?

lngLastRow = Sheets("Details").Cells(Rows.Count, "B").End(xlUp).Row
lngNewRow = Sheets("Search").Cells(Rows.Count, "A").End(xlUp).Row + 1
With Sheets("Search")
Select Case UCase(.Range("C2"))

Case "FIRST", "": TheCol = "I": TheCol1 = "J"
Case "SECOND": TheCol = "L": TheCol1 = "M"
Case "FINAL", "": TheCol = "N"

Case Else 'the default/error option
'TheCol = "J"
MsgBox "Check cell C2 on the Search sheet"
Exit Sub
End Select
For lngRow = 1 To lngLastRow

If (Sheets("Details").Range("H" & lngRow) Date - 90 Or
Len(.Range("E4").Value) = 0) And _
(Sheets("Details").Range("F" & lngRow) = .Range("E2") Or
Len(.Range("E2").Value) = 0) And _
(Sheets("Details").Range("E" & lngRow) = .Range("E6") Or
Len(.Range("E6").Value) = 0) And _
(Sheets("Details").Range(TheCol & lngRow) = .Range("C4") Or
Len(.Range("C4").Value) = 0) And _
(Sheets("Details").Range(TheCol1 & lngRow) = .Range("C6") Or
Len(.Range("C6").Value) = 0) Then

varTemp = Sheets("Details").Range(lngRow & ":" & lngRow)
Sheets(4).Range(lngNewRow & ":" & lngNewRow) = varTemp
lngNewRow = lngNewRow + 1
End If
Next
End With

its gives me an error only when I select "Final" in C2

I was able to rectify this.. :)

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 ***