VBA to Check for a set of Characters in a Variable name

[FuzzyTom]

Hi,

I tried to use this code in VBA

if ClName.Formula = Evaluate("=if(iserror(find("ccs",ClName)),0,1) "=" 1 then

I know it is not correct, but here is what I am hoping to achieve.

I would like to check the variable ClName to see if it has an extension of "ccs". For example the clients name, ClName, might be Dennis Morley which is a general client but then I might have a Brian Shaw-ccs and my VBA code above was my attempt to see if I could identity this client in a list of clients. As I loop thru these clients I will me naming the Tabs after each client and and giving each tab a distinctive color ... the clients with the "ccs" extension will have a different tab color than the general clients.

Any help, direction, pointers and tips would be appreciated.

Thanks.

 

[FuzzyTom]

Okay ... ClName is a string so I cannot use it as I have shown.

So I revise my question and ask instead is there a way for me to use evaluate on the Variable ClName such that it would correctly identify the case where I have a ClName with an extension -ccs?

I also revised the Evaluate statement somewhat ... it is now:

= Evaluate("=if(if(iserror(find(""ccs"",ClName)),0,1)" = " 1)") then ... the rest I can take care of once I can get this to work.

Thanks.

 

[Stridhan]

if you have it in a variable why dont use the vba inStr() function ?

 

[FuzzyTom]

Thanks Stridhan ... I will test your suggestion and let you know how it works soon.

 

[FuzzyTom]

Hi Stridhan,

I have ClName defined as a string in a range called ClientWorkName so my code is:

For each l in range(ClientWorkName)
ClName = l.value
TabName = ClName

So later on in the code when I test to see if ClName has a "ccs" extension it still sees ClName as part of the range I think because when I run this:

if instr(1, Clname, "css") <> 0 then

I get this flag "Subscript out of range"

Any thoughts?

Thanks.

 

[FuzzyTom]

I think I have a solution in this link:

Wildcard replacement when using SUMPRODUCT

I will have to adapt it to my situation and also so that it can be written in VBA ... Thanks

 

[FuzzyTom]

mikeerickson came up with a solution ... thanks to all .... here is the link again:

Wildcard replacement when using SUMPRODUCT

 

[Stridhan]

thanks for the feedback, great suggestion from mikeerickson!

We learn alot from this page, each and every day

 

[FuzzyTom]

So true ... another site that I find helpful is:

Excel Redirect

thanks.