I have a search formula that resides in a cell. The idea is if
something is found return a 1 if not return 0 based on conditional
formatting, a color is assigned. This array seeking the max value for
a true instance.
Additional piece of info, the search is used a column value, many
contain 25 states and we need to include that 1 instances.
{=IF($B8<>"",
MAX(
NOT(ISERROR(SEARCH($A8,Escalations!$A$5:$A$65000)))*
NOT(ISERROR(SEARCH($B8,Escalations!$B$5:$B$65000)))*
NOT(ISERROR(SEARCH(Country!D$7,Escalations!$C$5:$C$65000)))*1),
MAX(
NOT(ISERROR(SEARCH(Country!D$7,Escalations!$C$5:$C$65000)))*
NOT(ISERROR(SEARCH($A8,Escalations!$A$5:$A$65000)))*1))}
To change this into a vba function, I have named ranges and I would
pass in the A8, B8 and D7 information to yield a result. For example:
Public Function test(Col1 As String, Col2 As String, Hdr As String)
If Col2 <> "" Then
MAX(NOT(ISERROR(SEARCH(Col1,EscalationsData)))*
NOT(ISERROR(SEARCH(Col2,EscalationsData)))*
NOT(ISERROR(SEARCH(hdr,EscalationsData)))*1)
Else
MAX(NOT(ISERROR(SEARCH(hdr,EscalationsData)))*
NOT(ISERROR(SEARCH(Col1$A8,EscalationsData)))*1))
End If
End Function
Here are my questions and any insight would be great:
1. Is this feasible to code
2. Can I use a named range
3. Do I need to use "evaluate" command to get this to work
4. Should I build in a string to execute.
Again, Thank you in advance as I have been stumped for a while
FYI = EscalationData is a range created by:
=OFFSET(Escalations!$A$4,0,0,COUNTA(Escalations!$A:
$A),COUNTA(Escalations!$4:$4))
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
