View Full Version : [SOLVED] If Cell in range = 0 then three different scenarios

10-14-2013, 12:34 PM
Hi All,
It's been a while since I've written anything and I am trying to update an already existing code. Basically if all of the cells in a range = 0 then strLTS = "not calculated", if some of the cells in the range = 0 then strLTS = worksheetfunction.max (range) minus worksheet function.min(another range) and if none of the cells = 0 then strLTS = WorsheetFunction.Max (range).
So two questions:
1) How do I determine if the cells in a range = 0, I can't remember the syntax for match (or is there a better function to use?)

2) I plan on using elseif. I know the "any" scenario should be second, but does it matter if the "all" or "none" scenarios are first?

Thank you for your help!

10-15-2013, 01:42 AM
untested, try (air code) untested:

Select Case Application.CountIf(Range, 0)

Case Is = Range.Cells.Count: strLTS = "not calculated"
Case 0: strLTS = Application.Max(Range)
Case Is < Range.Cells.Count: strLTS = Application.Max(Range) - Application.Min(AnotherRange)
End Select

10-15-2013, 08:49 AM
That worked great. Thank you! Much cleaner than the way I was going.