Skip condition if cell is blank

K

Kash

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
 
P

p45cal

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
 
K

Kash

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)
 
P

p45cal

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
 
K

Kash

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
 
P

p45cal

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
 
K

Kash

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
 
P

p45cal

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
 
K

Kash

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
 
P

p45cal

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

Its giving me a run time error..
 
K

Kash

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
 
P

Phillip Holmes

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Search Option 1
Search letters within Words from the List 2
TransferSpreadsheet and NamedRange; not working! 3
Insert Page Break Based on Text 5
Feach data according to cell value 0
Get data - Multiple criteria 0
Grouping According to IndentLevel 6
A simple if <> nn then Msgbox "No Match" 5

Top