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

Hi,

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
Then
ActiveCell.Offset(0, 1).Select
Else
ActiveCell.EntireColumn.Delete
LastCol = LastCol - 1
End If
Loop

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

Thanks,
Peatawn


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:

Code:
SELECT CASE ActiveCell.Value
    CASE "resRespondent", "ResLastCallDate", "LastCallStartTime", "resCallCount"
        ActiveCell.Offset(0, 1).Select
    CASE ELSE
        ActiveCell.EntireColumn.Delete
        LastCol = LastCol - 1
END SELECT
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
.


Reply

 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 07:58 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