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