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?
 
Rory ArchibaldConnect With a Mentor Commented:
=IF(SUMPRODUCT(COUNTIF(A1,"*"&{"Service","Assembly","SM","MA"}&"*"))>0,"This is the responsibility of MSC","")
for example.
Regards,
Rory
0
 
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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 houdiniConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.