Windows 7 Forums
Welcome to Windows 7 Forums. Our forum is dedicated to helping you find support and solutions for any problems regarding your Windows 7 PC be it Dell, HP, Acer, Asus or a custom build. We also provide an extensive Windows 7 tutorial section that covers a wide range of tips and tricks.

Windows 7: VBA - Test cell for multiple values

12 Apr 2013   #1

Windows 7 Home (x64)
VBA - Test cell for multiple values


I built a large macro a while back that I need to reduce because with the recent additions I have made, I got "Procedure too large" as an error upon execution. I've already condensed most of my coding to something more elegant (I keep learning new things every time). Now I'd like to reduce the following code :

Do Until ActiveCell.Column = LastCol + 1
If ActiveCell.Value = "resRespondent" Or ActiveCell.Value = "ResLastCallDate" Or ActiveCell.Value = "LastCallStartTime" Or ActiveCell.Value = "resCallCount" _ ''And it goes on like this for many more values
ActiveCell.Offset(0, 1).Select
LastCol = LastCol - 1
End If

This is actually a big chunk of code and I have many more of these chunks in my macro. The problem is always having to repeat "Or ActiveCell.Value = " before each test. Something along the following idea would be great:

If ActiveCell.Value = "this", "that", "or this", "or that" Then


My System SpecsSystem Spec

12 Apr 2013   #2

Windows 7 Ultimate x64

You can use SELECT CASE instead of IF, which allows such multiple values when comparing the same variable to many constants:

SELECT CASE ActiveCell.Value
    CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
        ActiveCell.Offset(0, 1).Select
        LastCol = LastCol - 1
Which is functionally equivalent to your example. Try it, as I've not actually tested it.
My System SpecsSystem Spec
13 Apr 2013   #3

Windows 7 Home (x64)

Thanks Alejandro! This works perfectly. Not only did you help me reduce my code, but you also taught me something new.
My System SpecsSystem Spec


 VBA - Test cell for multiple values

Thread Tools

Similar help and support threads for2: VBA - Test cell for multiple values
Thread Forum
Solved bootoptimizefunction is missing values Performance & Maintenance
Are these normal values for a 3Gb/s ssd Hardware & Devices
Hex values of fonts General Discussion
Is there any test software can test or verify Displayport? Hardware & Devices
Help interpreting SMART values Hardware & Devices
How to replace REG_BINARY values ? Performance & Maintenance
Anyone with a HD 5850 run a test on Passmark performance test? Graphic Cards

Our Sites

Site Links

About Us

Find Us

Windows 7 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 7" and related materials are trademarks of Microsoft Corp.

Designer Media Ltd

All times are GMT -5. The time now is 05:00 AM.
Twitter Facebook Google+

Windows 7 Forums

Seven Forums Android App Seven Forums IOS App

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33