PDA

View Full Version : Solved: Select Case not case sensitive



Djblois
11-05-2007, 12:18 PM
I am using a Select Case statement to find Column Names that may have been typed in by a user:

Select Case Selection.Value
Case "Date", "ETD", "Reqst'd ETD", "Actual ETA", "ETA"
comAddNumFormat Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)), "mm/dd/yy;@"
Case "Whse"
Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)).Insert shift:=xlToRight
comAddWhse Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)), fnFindColumnDif(lColNum, "Whse#")
Case "Cust#"

That is only a piece of the select case statement but it is all very similar to the rest. It works perfectly except I don't want it to be case Sensitive. I want it to find "Date" or "date" or "DATE" to be the same. Is this possible?

Norie
11-05-2007, 12:56 PM
Daniel

Why not use UCase?

And put all the values to check for in upper case?

Select Case UCase(Selection.Value)
Case "DATE", "ETD", "REQST'D ETD", "ACTUAL ETA", "ETA"

PS Why/how are you using Selection?

Djblois
11-05-2007, 01:05 PM
Not a Bad idea. Thank you.

Norie
11-05-2007, 01:10 PM
Daniel

Glad to help.

Now is there any chance you could answer my question?:)

Your using Selection and you also have unqualified references to Range/Cells.

Both of these could cause you problems in the future.

Charlize
11-05-2007, 02:43 PM
I'm not sure about this one, butOption Compare Textdoes this work in this case to ?

Djblois
11-06-2007, 08:27 AM
Norie,

What do you mean by unqualified references? I would be glad to answer that question.

Is this what you meant I should be doing:

Select Case UCase(Range(Cells(1, lColNum)).Value)
Case "DATE", "ETD", "REQST'D ETD", "ACTUAL ETA", "ETA"
comAddNumFormat Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)), "mm/dd/yy;@"
Case "WHSE"
Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)).Insert shift:=xlToRight
comAddWhse Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)), fnFindColumnDif(lColNum, "Whse#")
Case "CUST#"
comAddNumFormat Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum)), "0000"

Norie
11-06-2007, 08:37 AM
Daniel

What I mean is that whenever you have something like this:

Range(Cells(2, lColNum), Cells(finalrow(wsWorking), lColNum))
That neither Range or Cells have a reference to any worksheet.

Without a worksheet reference then VBA will assume you mean Range/Cells on whatever it considers the active worksheet.

Now you might get away with that but it ain't guaranteed.::bug:

By the way why have you created subs like comAddNumFormat for, I assume anyway, methods that are built-in to VBA. ie NumberFormat.

Djblois
11-06-2007, 08:40 AM
comAddNumFormat does more than just adds the number format: This is the code that it does:

With rngChange
.Value = rngChange.Value 'Makes sure the number is saved as a number and not text
.NumberFormat = stgFormat
End With

I used to have to do that all the time seperately for every column. I did it so many times that I created a seperate sub to cut down on the amount of lines.

Now back to your other question? Should I use a worksheet reference on both range and cells or just one or the other?

Norie
11-06-2007, 08:44 AM
Daniel

That's 2 lines of code essentially.

Seems to me a bit of overkill to create a sub for such a thing.

But if it works for you, it works for you.:)

Simon Lloyd
11-06-2007, 09:05 AM
Charlize, this: Option Compare Text should work but the user will always have to remember to change the state back to case sensitive with Option Compare Binary, whereas Norie's Select UCase(Selection.Value) is only active when required.

Djblois
11-06-2007, 09:18 AM
Thank you all for your help