MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If (big or small)

Posted by Joe C on January 25, 2002 11:19 AM

How do you use a VBA if statement not concerned about upper or lower case of letters.

Posted by Joe Was on January 25, 2002 12:12 PM

It depends on what you are doing, in the future don't ask if you don't supply an example!

If you whant to locate text in a sheet if you want it to locate the text and be case sensitive, use the "Find" method. If you do not want the location to be case sensitive use the "Search" method.

Most methods and functions have ways to deal with or without case sensitivity. You can also use text functions to clean up both the data and the criteria before you work with them. I use the "Proper(ref)" function to clean data, but others work too. Like, Upper, Lower, Trim, Clean, etc. JSW

Posted by Joe C on January 25, 2002 2:09 PM

I'm sorry I should have explained better.
I have a program where it goes into another persons file and checks a specific column for four or five possible otions then works that column based on text.
Since all the people will be using differnt files I cant really force them to use caps or lowercase.
I was going to use simple
if cells(2,x) = "Many" then
the text will trigger a certain sub. Only there are many ways to write many. I couldnt figure a way to solve this except with an annoying list of or with each possible way.

Posted by Barrie Davidson on January 25, 2002 2:43 PM

How about converting that value to uppercase and then using your IF statement? Something like

If UCase(ActiveCell.Value) = "MANY" Then 'whatever actions you want

This will convert the active cell's value to uppercase and it won't matter whether it is "Many", "MaNy", etc.


Barrie Davidson

Posted by Joe C on January 28, 2002 10:04 AM

This worked like a charm.