	AllExperts > Excel
	Search

Volunteer
Answers to thousands of questions

Home · More Excel Questions · Answer Library

· Encyclopedia ·

Excel - IF (multiple match tests) THEN

Expert: Chad Welch - 10/11/2009

Question
I have 'inherited' a spreadsheet at work that includes this line in it :
If DAT17 = "" And DAT18 = "" And DAT19 = "" And DAT20 = "" And DAT21 = "" And DAT22 = "" And DAT23 = "" And DAT24 = "" And DAT25 = "" And DAT26 = "" And DAT27 = "" And DAT28 = "" And DAT29 = "" And DAT30 = "" And DAT31 = "" And DAT32 = "" And DAT33 = "" And DAT34 = "" And DAT35 = "" And DAT36 = "" And DAT37 = "" Then LT=LT+1.
Is there a more elegant, compact way of doing this. The current code works well enough but seems very cumbersome.

Thanks

Answer
Hi Paul,
VBA programming is a very literal language, so it is necessary to be this specific. If you need to ensure that every variable is an empty string, then you have to check each one. That said, it is sometimes possible to rethink the approach to the problem. In this case you are checking that each variable is an empty string. You could also concatenate all of the strings (with &) and check if the final string is still empty:

If DAT17 & DAT18 & DAT19 & DAT20 & DAT21 & DAT22 & DAT23 & DAT24 & DAT25 & DAT26 & DAT27 & DAT28 & DAT29 & DAT30 & DAT31 & DAT32 & DAT33 & DAT34 & DAT35 & DAT36 & DAT37 = "" Then LT = LT + 1

I don't know that this is any more elegant, but it is more compact.

Best regards,
Chad Welch

View Follow-Ups Add to this Answer Ask a Question

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here