Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Using LIKE in case statement
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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:

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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,494
Default 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
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 2,718
Default 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
...
| 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
|

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Using LIKE in case statement
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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 153
Default Using LIKE in case statement
Gary, thank you for your effort. I have used Jim's solution, because it is
easier for me to understand.

"Gary Keramidas" wrote:

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
...
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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default Using LIKE in case statement
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
...
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


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Using Instr in a Word Case Statement
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Case Statement jlclyde Excel Discussion (Misc queries) 3 December 4th 08 05:04 PM
Anyone actually get Case statement to work ... ? ForestFeeder Excel Worksheet Functions 3 April 21st 06 04:14 PM
Case Of Statement hfazal Excel Programming 2 February 14th 06 08:18 PM
Case Statement Help stck2mlon Excel Programming 3 June 2nd 04 01:44 PM
Case statement smi Excel Programming 2 October 18th 03 02:20 PM


All times are GMT +1. The time now is 04:17 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement