Do Search Criteria

[Godders199]

Hi, sorry if title wrong, dont really know what to call this

Have the following code which excludes any rows where the critera given is met.

Do Until Range("A" & R).Value = ""
If Sheets("checkSmart").Range(QRiskRule1 &R).Value = "Annual Review" OrSheets("checkSmart").Range(QAdvType & R).Value ="Other" Or Sheets("checkSmart").Range(QQueuestatus &R).Value = "Adviser Contact Complete" Or Sheets("checkSmart").Range(QAdvType &R).Value = "SImon" Or Sheets("checkSmart").Range(QAdvType& R).Value = "Jane" OrSheets("checkSmart").Range(QAdvType & R).Value = "Pete"Or Sheets("checkSmart").Range(QAdvType & R).Value = "Jackie"Or Sheets("checkSmart").Range(QAdvType & R).Value = "Ian"Or Sheets("checkSmart").Range(QAdvType & R).Value = "Andrew"Or Sheets("checkSmart").Range(QSubmissionDate & R) <= dateRngThen

Else

Curently no row at status "advise contact complete" pulls through


However i need to change part so it considers two columns together, this is what i am trying

Sheets("checkSmart").Range(QQueuestatus& R).Value = "Adviser Contact Complete" &Sheets("checkSmart").Range(QFailureDescription & R).Value ="Pass"

I have tried, "&" as shown,putting it in Brackets, taking the 2nd Sheets "checkSmart" out.

but thing works.

With the above solution it pulls through all rows with "adviser contact complete", when i only want those which are at Status "adviser contact complete" with a failure description that is not "pass"

Any ideas?

 

[wideboydixon]

I think you should split the If statement over multiple lines like this:

Do Until Range("A" & R).Value = ""
    If Sheets("checkSmart").Range(QRiskRule1 & R).Value = "Annual Review" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Other" _
    Or (Sheets("checkSmart").Range(QQueuestatus & R).Value = "Adviser Contact Complete" And Sheets("checkSmart").Range(QFailureDescription & R).Value = "Pass") _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Simon" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Jane" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Pete" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Jackie" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Ian" _
    Or Sheets("checkSmart").Range(QAdvType & R).Value = "Andrew" _
    Or Sheets("checkSmart").Range(QSubmissionDate & R) <= dateRng Then
    Else

WBD

 

[Godders199]

thanks , the only thing i had not tried was "and" rather than "&"

works prefectly now , thanks