I'm trying to categorize certain products into one of three segments based on
their name. Since I have about 100 products, I'd rather not write a "like"
statement for each and every product. Is there a way to combine them so I
can do something like the following?

If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" Then
Range("W2").Offset(i, 0).Value = "Segment1"

Currently I get a type mismatch error on the first of the lines shown here.
Any suggestions?

Rather than this:
If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" ...

YOu need this:
If Range("H2").Offset(i, 0).Value Like "*prod1*" Or Like "*prod2*"

"StumpedAgain" wrote:

> I'm trying to categorize certain products into one of three segments based on
> their name. Since I have about 100 products, I'd rather not write a "like"
> statement for each and every product. Is there a way to combine them so I
> can do something like the following?
>
> If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" Then
> Range("W2").Offset(i, 0).Value = "Segment1"
>
> Currently I get a type mismatch error on the first of the lines shown here.
> Any suggestions?
>

If Range("H2").Offset(i, 0).Value Like "*prod1*" _
Or Range("H2").Offset(i, 0).Value Like "*prod2*" Then

or maybe...

If lcase(Range("H2").Offset(i, 0).Value) Like "*prod1*" _
Or lcase(Range("H2").Offset(i, 0).Value) Like "*prod2*" Then


StumpedAgain wrote:
>
> I'm trying to categorize certain products into one of three segments based on
> their name. Since I have about 100 products, I'd rather not write a "like"
> statement for each and every product. Is there a way to combine them so I
> can do something like the following?
>
> If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" Then
> Range("W2").Offset(i, 0).Value = "Segment1"
>
> Currently I get a type mismatch error on the first of the lines shown here.
> Any suggestions?
>

--

Dave Peterson

I tried this but I still get the mismatch error. I think when I use the "Or"
it wants to match numerical values? Any other thoughts/suggestions?

"Sam Wilson" wrote:

> Rather than this:
> If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" ...
>
> YOu need this:
> If Range("H2").Offset(i, 0).Value Like "*prod1*" Or Like "*prod2*"
>
> "StumpedAgain" wrote:
>
> > I'm trying to categorize certain products into one of three segments based on
> > their name. Since I have about 100 products, I'd rather not write a "like"
> > statement for each and every product. Is there a way to combine them so I
> > can do something like the following?
> >
> > If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" Then
> > Range("W2").Offset(i, 0).Value = "Segment1"
> >
> > Currently I get a type mismatch error on the first of the lines shown here.
> > Any suggestions?
> >

Thanks Dave. the first of the two works. I was just trying to cut more
corners than was allowed.

"Dave Peterson" wrote:

> If Range("H2").Offset(i, 0).Value Like "*prod1*" _
> Or Range("H2").Offset(i, 0).Value Like "*prod2*" Then
>
> or maybe...
>
> If lcase(Range("H2").Offset(i, 0).Value) Like "*prod1*" _
> Or lcase(Range("H2").Offset(i, 0).Value) Like "*prod2*" Then
>
>
> StumpedAgain wrote:
> >
> > I'm trying to categorize certain products into one of three segments based on
> > their name. Since I have about 100 products, I'd rather not write a "like"
> > statement for each and every product. Is there a way to combine them so I
> > can do something like the following?
> >
> > If Range("H2").Offset(i, 0).Value Like "*prod1*" Or "*prod2*" Then
> > Range("W2").Offset(i, 0).Value = "Segment1"
> >
> > Currently I get a type mismatch error on the first of the lines shown here.
> > Any suggestions?
> >
>
> --
>
> Dave Peterson
>