VBA-"Instr": Also possible in an ordinary excel formula?

Dear Experts:

below macro does the following:

Cell G7 will show "This is the responsibility of MSC" whenever the text entered in Cell C7 contains the following strings: "Service", or "Assembly" or "SM" or "MA"

The macro is running fine. I wonder whether an Excel formula can achieve the same result?

Help is much appreciated. Thank you very much in advance.

Regards, ANdreas
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 7 And Target.Column = 3 Then 'This routine will only progress if cell C7 is changed
        If InStr(1, Target, "Assembly", vbTextCompare) Or InStr(1, Target, "Service", vbTextCompare) Or InStr(1, Target, "MA", vbTextCompare) Or InStr(1, Target, "SM", vbTextCompare) Then
            Range("G7").Value = "This is the responsibility of MSC"
        Else
            Range("G7").Value = ""
        End If
    End If
End Sub

Open in new window

Andreas HermleTeam leaderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

palinitrCommented:
This will work in Excel 2007:

=IF(OR(C7 = "Assembly", C7 = "Service",C7="MA",c7="SM"),"This is the responsibility of MSC","")
0
RunriggerCommented:
=if(or(FIND("service",c7,1)>0,FIND("assembly",c7,1)>0,FIND("sm",c7,1)>0,FIND("ma",c7,1)>0),"Blah blah","")
0
Rory ArchibaldCommented:
=IF(SUMPRODUCT(COUNTIF(A1,"*"&{"Service","Assembly","SM","MA"}&"*"))>0,"This is the responsibility of MSC","")
for example.
Regards,
Rory
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Rory ArchibaldCommented:
Dave,
Yours will return an error unless all of them are in the cell.
Reegards,
Rory
0
Rory ArchibaldCommented:
It's also case-sensitive if you use FIND (unlike SEARCH).
0
RunriggerCommented:
Not sure why as the if condition tests for the existance of OR one of them = true, then "blah blah" will be returned, I deliberately did not do an AND?

Always happy to learn though
Dave
0
RunriggerCommented:
I also forgot to include the "false" on the context find option, easily entered though.

0
Rory ArchibaldCommented:
OR will test all of them. If any of them fail, you'll get an error (unless you enclose each in an ISNUMBER() formula) so the OR will return an error.
0
RunriggerCommented:

lets say C7 includes the string "i created a service that helps users"

The formula results in this
or (
FIND("service",c7,1)>0 this will result in TRUE
FIND("assembly",c7,1)>0 this will result in FALSE
FIND("sm",c7,1)>0 this will result in FALSE
FIND("ma",c7,1)>0 this will result in FALSE
)

formula evaluates thus

if(or(true,false,false,false),"blah blah",does not succeed") then the cell will appear as - "blah blah"

or(true,false,false,false) = true


0
Rory ArchibaldCommented:
Did you try this?
It evaluates to:
or (
13>0 this will result in TRUE
#VALUE!>0 this will result in #VALUE!
#VALUE!>0 this will result in #VALUE!
#VALUE!>0 this will result in #VALUE!
)

which results in #VALUE!
0
RunriggerCommented:
arrrgh, you got me, of course it will!!!!

I am knackered, clearly the 10 pints beers, a couple of G&T's, half a bottle of wine and a Ruby Murray last night has affected my brain power today.

you need to clarify your formula though;

=IF(SUMPRODUCT(COUNTIF(A1,"*"&{"Service","Assembly","SM","MA"}&"*"))>0,"This is the responsibility of MSC","")

squirly barckets {} indicates an array formula, some users may not know how to do that, alternatively, if the above formula is correct, for the heathens like me lacking the knowledge to understand it, but clearly wanting to seek the knowledge, please divulge.

Cheers
Dave
0
Rory ArchibaldCommented:
The formula is correct - the curly brackets represent an array constant. It is not an array formula as such - I.e. it does not need to be array-entered.
0
RunriggerCommented:
ahhhh, not come across those before, thanks
0
RunriggerCommented:
I like the use of the "*" both before and after!
0
Andreas HermleTeam leaderAuthor Commented:
Dear all,

wow, what a terrific support. I will "sift" thru all your answers and get back to you soon.

Thank you so much.

Regards, Andreas
0
barry houdiniCommented:
Rory's suggested formula will also work with SUM in place of SUMPRODUCT.....or you could also USE SEARCH and COUNT like this
=IF(COUNT(SEARCH({"Service","Assembly","SM","MA"},A1)),"This is the responsibility of MSC","")
regards, barry
0
Andreas HermleTeam leaderAuthor Commented:
Dear rorya:

your code is working just fine. The formula looks awesome, that's  for sure. I am still trying the others.  I really, really do appreciate your knowledge in Excel. Superb.

Great, terrific support from your side. I guess I will award the points to you, but let me try the others and then I'll see.

Again, thank you very much. Regards, Andreas
0
Rory ArchibaldCommented:
Barry's will work too (and is neater, IMO) so he should get a share of the points too, I think.
0
Andreas HermleTeam leaderAuthor Commented:
Dear rorya and Barry:

both formulas are working. Great!. Since rorya was the quickest I suggest splitting the points 400:100. I hope this conforms with the point awarding rules. If not, please let me know. If I do not hear back from you within one day or so regarding the points distribution I will award them as suggested.

Again, thank you very much for your terrific and professional support. This forum is just great.

Have a nice weekend. Regards, Andreas
0
Rory ArchibaldCommented:
I might have been first, but I think Barry's was better (as usual) so I'd say a 50:50 split, if that's OK with you both?
0
barry houdiniCommented:
What to say?
I'm happy with whatever Andreas decides, I would suggest the majority to Rory, not only for the formula but for other contributions/explanations
regards, barry
0
Andreas HermleTeam leaderAuthor Commented:
Hi,

ok, I am gonna do a 60:40 split. Thank you again. Regards, Andreas
0
Andreas HermleTeam leaderAuthor Commented:
Terrific support!
Regards, Andreas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.