PDA

View Full Version : Solved: case yes-no, can the code be smaler?



Ago
06-20-2008, 03:01 PM
im creating a searchfunction and i want the user to select if its going to be case sensitive or not.
at this early stage i use a select case msgbox.

when the user has clicked yes or no, do i need to make two searchcodes?
the code is not very big but i want it to look nice.



Select Case MsgBox("Should it be case sensitive?", vbYesNo + vbQuestion)
Case vbYes

Case vbNo

End Select
While a < n1

If Sheets("Big IP collection").Range("E" & a).Value Like pname And Not Sheets("Big IP collection").Range("E" & a).Value Like exclude Then

Sheets("Sheet1").Range("A" & b).Value = Sheets("Big IP collection").Range("E" & a).Value
Sheets("Sheet1").Range("B" & b).Value = Sheets("Big IP collection").Range("G" & a).Value
b = b + 1
End If

a = a + 1
Wend




the only thing that would be the diffrence in those two codes (uppercase/lowercase) would be the ifstatement.


If Sheets("Big IP collection").UCase(Range("E" & a).Value) Like pname And Not Sheets("Big IP collection").UCase(Range("E" & a).Value) Like exclude Then


it feels stupid making two identical codes.

any suggestions?

Bob Phillips
06-20-2008, 03:25 PM
With Sheets("Big IP collection")

vbans = MsgBox("Should it be case sensitive?", vbYesNo + vbQuestion)

While a < n1

If (vbans = vbYes And .Range("E" & a).Value Like pname And _
Not .Range("E" & a).Value Like exclude) Or _
(vbans = vbNo And UCase(.Range("E" & a).Value) Like UCase(pname) And _
Not UCase(.Range("E" & a).Value) Like UCase(exclude)) Then

Sheets("Sheet1").Range("A" & b).Value = .Range("E" & a).Value
Sheets("Sheet1").Range("B" & b).Value = .Range("G" & a).Value
b = b + 1
End If

a = a + 1
Wend
End With

mikerickson
06-20-2008, 03:29 PM
Dim testString as String
testString = Sheets("Big IP collection").Range("E" & a).Value
if MsgBox("Should it be case sensitive?", vbYesNo + vbQuestion) = vbNo Then
testString = UCase (testString)
End If
If testString like pname and not(testString like exclude) then
Rem code
end if

Ago
06-20-2008, 03:29 PM
thats a good way of doing it.
never thought of that.

thanks!!

Ago
06-20-2008, 03:31 PM
thats a even better way mike! very nice!

mikerickson
06-20-2008, 03:33 PM
Xld's code should be used unless pname and exclude are all uppercase.