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:
Re: Using LIKE in case statement
06-Oct-08
this seemed to work, don't know how kosher it is
Sub test(
Dim CellB2 As Varian
Dim Prime As Varian
Dim i As Lon
CellB2 = Range("B2").Valu
Select Case i >
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 Selec
Range("B2").Activat
ActiveCell.Value = Prim
End Su
--
Gar
Previous Posts In This Thread:
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 Varian
Dim Prime As Varian
CellB2 = Range("B2").Valu
Select Case CellB
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 Selec
Range("B2").Activat
ActiveCell.Value = Prim
Basically, if this were SQL, it would be
SELECT CASE WHEN CellB2 LIKE 'Jnet' THEN 'Jnet
WHEN ....
EN
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
Re: 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 the
prime = "JNET
elseif instr(1,cellb2,"mastec",vbtextcompare) > 0 the
Prime = "Mastec
elseif instr(1,cellb2,"ivy",vbtextcompare) > 0 the
prime = "Ivy
...
end i
if prime = "" the
'no matching string
els
'at least one matching strin
end i
I don't see the value of using select case in this case <bg>
Greg Snidow wrote
--
Dave Peterson
Re: Using LIKE in case statement
this seemed to work, don't know how kosher it is
Sub test(
Dim CellB2 As Varian
Dim Prime As Varian
Dim i As Lon
CellB2 = Range("B2").Valu
Select Case i >
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 Selec
Range("B2").Activat
ActiveCell.Value = Prim
End Su
--
Gar
Re: Using LIKE in case statement
This is one way to go
CellB2 = Range("B2").Valu
Select Case Tru
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
case
the
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.
:
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.
:
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)
Submitted via EggHeadCafe - Software Developer Portal of Choice
Adding WCF Service References
http://www.eggheadcafe.com/tutorial...9-dfa51a9fab8e/adding-wcf-service-refere.aspx