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
Peatawn

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
Alejandro85

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
Peatawn

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
Thread Forum
Can I compare 2 Excel spreadsheets cell by cell?
I have an Excel 2010 workbook comprising a simple organization membership list, consisting of names, addresses, etc., for nearly 2,000 members, grouped into 5 chapters' sheets, in a single Excel 2010 file. Every time I sit down to enter updates for any member, or add new members, etc., I always ...
Microsoft Office
6 Cell Vs 9 Cell Battery for Laptop?
Someone needs to get a new battery for a very old laptop they have. The battery doesn't work so they have to have it plugged all the time otherwise it wouldn't work. Laptop is 15.6 and sort of heavy but the thing is they never take the laptop outside the house. It will mainly be used on one floor...
Hardware & Devices
Are these normal values for a 3Gb/s ssd
Hi, I'm running a Toshiba Tecra R840-16J laptop (i7-2640M, 8 GB RAM, Plextor M5Pro 512 GB). I know my SATA is 3 Gb/s...:(. I runned a quick AS SSD bechmark and the values seems a little low! What could be the cause? Any sugestion? Cheers, paulo
Hardware & Devices
Can't add values to Excel graph.
I just can't get new data to display on this graph (see below): http://www.mediafire.com/imgbnc.php/628220c5ad14e56fcc9c18f3a30331450461e373d13e7498ffb7e5c39e8d57af6g.jpg I enter the values in the table on the left, and they won't show up in the graph! This has just started happening, as you...
Microsoft Office
Hex values of fonts
Hi, Any idea where can I get the Hex value for different fonts. I am looking for the Hex value for Helvetica Neue so that I can create a registry entry to make it as the default font. In other unrelated threads I could find the ones for Verdana and Calibri Calibri =...
General Discussion
Help interpreting SMART values
So I just lost 2 Western Digital 1TB HDDs (WD10EARS and WD10EADS) due to unknown causes. One went dead dead, the other one just nuked my Win7 partition and started arbitrary denying access to the other, data partition. Weird as hell since SMART never indicated anything wrong, they simply suddenly...
Hardware & Devices


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 18:08.

Twitter Facebook Google+



Windows 7 Forums

Seven Forums Android App Seven Forums IOS App