You are here:
- Home
- Computing/Technology
- Business Software
- Excel
- IF (multiple match tests) THEN
Excel/IF (multiple match tests) THEN
Advertisement
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
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
Related Articles
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
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
Excel
Answers by Expert:
- Mary A. Lindsey
- Nathan Head
- Raj Narayan Banerjee
- Chad Welch
- Ken Cowen
- Damon Ostrander
- Gyula Gulyas
- Stuart Resnick
- Bill Hermanson
- Jerry Beaucaire
- Michelle Howell
- Isaac
- Edward H. Mori
- Aidan Heritage
- Tom Ogilvy
- Richard Roberts
- Richard Stephens
- JP
- Richard Rost
- Victor Lan
- Tushar Sakhalkar
- Philip Buckley
- David W. Cofer
- Amal
- Bob Umlas
- Jessica L.
- Shahid
- Jan Karel Pieterse
- Craig
- Adelaide carvalho
- Sixthsense
- Bill
- Anne Troy
Chad Welch
Expertise
I am a heavy user of Excel and VBA. Unfortunately, I do not have the time to help you design a complex project. However, I am very happy to help troubleshoot problems you are encountering or provide direction with formulas or snippets of code.
Experience
I have created several complex spreadsheets for use in data collection/aggregation as well as scientific laboratory studies and procedures.
Organizations
Brainbench MVP for MS Excel.
Education/Credentials
AS - Computer Science (emphasis on programming)
BS - Chemistry
©2012 About.com, a part of The New York Times Company. All rights reserved.