Select Case Question

[extrapulp]

I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.

Please Login or Register  to view this content.

I can see a couple of other options but have not been able to find out if they are possible in excel. First an entry filter that would only allow the user to enter specific values would at least limit the letters they could enter. Second being able to strip the letter if there is a letter entered off the CellData entered would make the Select Case much simpler.

Any ideas or other options?

Thanks

[royUK]

What is CellValue?

[DonkeyOte]

Well first note that by default VBA unlike native XL is case sensitive (ie S <> s) so if you want to be case insensitive in your testing (ie s = S) then you can either use Option Compare Text at the head of your module or you can force both test & criteria to Upper Case pre-test (my preference)

If we assume that we only want to test the Letters

Please Login or Register  to view this content.

In the above I used a Replace call to remove the preceding number (Val) from the CellValue (ie removed 8 from 8s) -- this was just in case the denotation of type exceeded 1 Character (ie ST was different to S...), if you only ever need the last character you can use UCase(Right(CellValue,1)) instead.

[extrapulp]

RoyUK,

What is CellValue?

CellValue was just a reference to what the user entered. It could of be anything they typed in the cell. For some background this code will run in Workbook_Change event. The data entered is supposed to be something similar to "8s" or just "8" or "8v", but since there is no input filtering the user could conceivably enter "8 because I was sick" just because they can!

DonkeyOte,
I was planning on checking for uppercase and lowercase letters for every letter but the example you provided makes it one less step!
The user could conceivably put any number of characters in. The issue I have with just trimming it to 2 characters is that they sometimes work over 9 hours or partial hours so I could have a single number "8", or a number and a letter "8s", or "8.5", or "7.5s" or the same with numbers in the double digits like "10.5". I guess using your example I could check to see if there was more than one letter after removing the Val and put up an alert letting the user know that they can only enter one letter and clear the cell... Thanks for the reply! Got my wheels turning again!

[extrapulp]

Here is what I came up with after the replies I received triggered some different thinking.

Please Login or Register  to view this content.

This works.

Thanks for the replies!!

[DonkeyOte]

you could still use SELECT CASE over lots of IFS if you prefer:

Please Login or Register  to view this content.

could be

Please Login or Register  to view this content.

I personally find Select Case much easier to read through, debug & adapt in the long run...

[extrapulp]

DonkeyOte,

Thank you for that! I had tried this

Please Login or Register  to view this content.

But this gave errors so I figured that you couldn't do 'Or' in this situation! That is much more efficient. I just didn't know how to do it.

Thank you very much!