Solved: Where to start with multiple conditions

[kathyb0527]

I have a large project that I'm trying to break down into multiple sub routines, but I think I might have written myself into a corner. I need to look in a column, find cells containing "Blank" or "Quality" if ANY of the adjacent cells are >0 then the routine continues, if all are = 0 a Message appears and no further calculations are needed. I've tried to lay it out on paper, written numerous IF, AND and OR statements and have just completely hit a wall . Help, direction, sympathy and a straight jacket will all be greatly appreciated.

Thanks!
K

[Bob Phillips]

[vba]

Const TEST_COL As String = "B" 'adjust to suit
Const TEST_ADJ_COL As String = "C" 'adjust to suit
Dim LastRow As Long
Dim sformula As String

With ActiveSheet
LastRow = .Cells(.Rows.Count, TEST_COL).End(xlUp).Row
sformula = "SUMPRODUCT(--((" & TEST_COL & "2:" & TEST_COL & LastRow & "=""Blank"")+" & _
"(" & TEST_COL & "2:" & TEST_COL & LastRow & "=""Quality""))," & _
"--(" & TEST_ADJ_COL & "2:" & TEST_ADJ_COL & LastRow & ">0))"
If .Evaluate(sformula) = 0 Then

MsgBox "No matches"
Exit Sub
Else

'carry on
End If
End With
[/vba]

[kathyb0527]

XLD,
I get a type mismatch at the line:

If .Evaluate(sformula) = 0 Then

I'm not exactly sure what that means, could you explain please?

Thanks,
K

[Bob Phillips]

Did you change the code in ANY way?

[kathyb0527]

yes,
I adjusted the columns as noted and I changed
MsgBox "No matches" to "No Carryover".

Here is a copy of my spreadsheet.

Thank you,
K

[Bob Phillips]

K,

I don't get that error.

I get a no carryover message and it then exits.

[kathyb0527]

I recopied everything to another workbook and it seems to be working now. Thank you for checking it for me. I've been slowly looking through the sformula and I understand the concept, but the syntax is confusing me. Are you using the TEST_COL in the formula or just to define which cell values to use? And, can this concept be used to find partial values in cells?

Thanks again for the help, it's great to be able to learn from experts!


K

[Bob Phillips]

I just used TEST_COL and TEST_COL_ADJ to provide some flexibility. If you wanted columns other than B and C, or want to change it some time, it is much easier to change those constants than to change the formula.