Hi,
You can't usually just use worksheet functions in VBA.
VBA sometimes has functions with the same workings, and you can use those, but
worksheet functions need to be handled differently.
Also, to use a named range in VBA, you have to use:
Range("Name-of-Range")
Regards - Dave.
> Date: Wed, 10 Feb 2010 15:46:55 -0800
> Subject: $$Excel-Macros$$ Formula Array to VBA Code
> From: mark.ku...@gmail.com
> To: excel-macros@googlegroups.com
>
> 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
_________________________________________________________________
Link all your email accounts and social updates with Hotmail. Find out now
http://windowslive.ninemsn.com.au/oneinbox?ocid=T162MSN05A0710G
--
----------------------------------------------------------------------------------
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
