Thank you Dave. I think part of my problem is that I did not understand how
InStr worked, I guess that's what I get for copying a pasting and altering
code. Anyhow, thank you for the time, and I have used Jim's method of using
"*" in the code, not because it is any better or worse, but because it is
more like SQL, which I understand better.

"Dave Peterson" wrote:

I think I would use a bunch of if/then/elseif's:

Prime = ""
if instr(1,cellb2,"jnet",vbtextcompare) 0 then
prime = "JNET"
elseif instr(1,cellb2,"mastec",vbtextcompare) 0 then
Prime = "Mastec"
elseif instr(1,cellb2,"ivy",vbtextcompare) 0 then
prime = "Ivy"
....
end if

if prime = "" then
'no matching strings
else
'at least one matching string
end if



I don't see the value of using select case in this case <bg.

Greg Snidow wrote:

Greetings all. I am trying be able to use something akin to LIKE in a case
statement. I need to set the value of a variable, "Prime" depending on
whether cell B2 contains certain values, and I have tried the below, to no
avail.

Dim CellB2 As Variant
Dim Prime As Variant
CellB2 = Range("B2").Value
Select Case CellB2
Case InStr(1, CellB2, "Jnet")
Prime = "JNET"
Case InStr(1, CellB2, "Mastec")
Prime = "Mastec"
Case InStr(1, CellB2, "Ivy")
Prime = "Ivy"
Case InStr(1, CellB2, "S&N")
Prime = "S&N"
Case InStr(1, CellB2, "Danella")
Prime = "Danella"
End Select
Range("B2").Activate
ActiveCell.Value = Prime

Basically, if this were SQL, it would be

SELECT CASE WHEN CellB2 LIKE 'Jnet' THEN 'Jnet'
WHEN .....
END

When I run the macro, cell B2 ends up being blank, so either I am not
correctly creating the variable CellB2, or there is something wrong with the
CASE. Any ideas. Thank you.

Greg

--

Dave Peterson