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

[Ago]

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.

[VBA]

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


[/VBA]

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

[VBA]
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
[/VBA]

it feels stupid making two identical codes.

any suggestions?

[Bob Phillips]

[vba]

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
[/vba]

[mikerickson]

[VBA]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[/VBA]

[Ago]

thats a good way of doing it.
never thought of that.

thanks!!

[Ago]

thats a even better way mike! very nice!

[mikerickson]

Xld's code should be used unless pname and exclude are all uppercase.