Thanks Gary, I have been trawling for days looking for a similar Word solution for using Instr in a Select Case statement to interrogate a string of text.
I hadn't thought of your approach which was a reverse of my logic.
I am pleased to say it made sense when I read the code and it worked perfectly for me so many thanks. A frustrating problem has now been solved.
I don't know if it helped the original poster but it certainly helped me remove all the ugly elseif statements. :)
Cheers,
BJA
Gary Keramidas wrote:
Using LIKE in case statement
06-Oct-08
this seemed to work, don't know how kosher it is:
Sub test()
Dim CellB2 As Variant
Dim Prime As Variant
Dim i As Long
CellB2 = Range("B2").Value
Select Case i 0
Case i = InStr(1, CellB2, "Jnet")
Prime = "JNET"
Case i = InStr(1, CellB2, "Mastec")
Prime = "Mastec"
Case i = InStr(1, CellB2, "Ivy")
Prime = "Ivy"
Case i = InStr(1, CellB2, "S&N")
Prime = "S&N"
Case i = InStr(1, CellB2, "Danella")
Prime = "Danella"
End Select
Range("B2").Activate
ActiveCell.Value = Prime
End Sub
--
Gary
"Greg Snidow" wrote in message
...
Previous Posts In This Thread:
On Monday, October 06, 2008 1:16 PM
GregSnido wrote:
Using LIKE in case statement
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
On Monday, October 06, 2008 1:46 PM
Dave Peterson wrote:
Using LIKE in case statement
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:
--
Dave Peterson
On Monday, October 06, 2008 1:51 PM
Gary Keramidas wrote:
Using LIKE in case statement
this seemed to work, don't know how kosher it is:
Sub test()
Dim CellB2 As Variant
Dim Prime As Variant
Dim i As Long
CellB2 = Range("B2").Value
Select Case i 0
Case i = InStr(1, CellB2, "Jnet")
Prime = "JNET"
Case i = InStr(1, CellB2, "Mastec")
Prime = "Mastec"
Case i = InStr(1, CellB2, "Ivy")
Prime = "Ivy"
Case i = InStr(1, CellB2, "S&N")
Prime = "S&N"
Case i = InStr(1, CellB2, "Danella")
Prime = "Danella"
End Select
Range("B2").Activate
ActiveCell.Value = Prime
End Sub
--
Gary
"Greg Snidow" wrote in message
...
On Monday, October 06, 2008 1:54 PM
Jim Rech wrote:
Using LIKE in case statement
This is one way to go:
CellB2 = Range("B2").Value
Select Case True
Case CellB2 Like "*Jnet*"
Prime = "JNET"
Also...
Dim CellB2 As Variant
Dim Prime As Variant
CellB2 = Range("B2").Value
Select Case True
Case InStr(1, CellB2, "Jnet") 0
Prime = "JNET"
--
Jim
"Greg Snidow" wrote in message
...
case
the
On Monday, October 06, 2008 3:02 PM
GregSnido wrote:
Thank you Dave.
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:
On Monday, October 06, 2008 3:04 PM
GregSnido wrote:
Gary, thank you for your effort.
Gary, thank you for your effort. I have used Jim's solution, because it is
easier for me to understand.
"Gary Keramidas" wrote:
On Monday, October 06, 2008 4:06 PM
Rick Rothstein wrote:
If I understand what you are doing correctly, I'd consider using this
If I understand what you are doing correctly, I'd consider using this
one-liner instead...
If InStr(1, "*Jnet*Mastec*Ivy*S&N*Danella*", "*" & Range("B2").Value & _
"*", vbTextCompare) = 0 Then Range("B2").Value = ""
--
Rick (MVP - Excel)
"Greg Snidow" wrote in message
...
Submitted via EggHeadCafe - Software Developer Portal of Choice
Adding WCF Service References
http://www.eggheadcafe.com/tutorials...ce-refere.aspx