Add this formula in VBA

[suyogpat]

Hello All,

It might be a stupid question, I have below formula which I want to add in my VBA macro is not accepting the formula. What I need is macro to calculate this formula.

my excel formula is - =IF(SUMPRODUCT(--(D$3:D$7<>""),--ISNUMBER(SEARCH(D$3:D$7,A4))),"exclude","")

Anybody can help me to write this formula in VBA?I tried the recording function but problem is, its possible that I have to change the range every week in this formula. so recoding formula option wont work here. if anyone can solve, this will be a big help for me..

Thanks in advance for your help and time.

 

[Giordano Bruno]

Re: How to add this formula in VBA ?? Please help

Have you thought of giving the range a dynamic name which will update automatically. If you need help with dynamic ranges, the internet is your friend.

 

[suyogpat]

Re: How to add this formula in VBA ?? Please help

Hi,

Thank you for your reply. I am quite new to VBA.. I search on internet but could not find a relative context.. Can you help me with this ?

Thanks in advance for your help and time..

 

[NARAYANK991]

Re: How to add this formula in VBA ?? Please help

Hi ,

Use this line of code to return the result of the posted formula :

formularesult = Application.Evaluate("=IF(SUMPRODUCT(--(D$3:D$7<>""""),--ISNUMBER(SEARCH(D$3:D$7,A4))),""exclude"","""")")

The VBA variable formularesult will contain either a blank or the text string exclude.

 

[suyogpat]

Re: How to add this formula in VBA ?? Please help

Hi NarayanK991 ..
Works like a charm. This is what I was looking for. With few modifications in above code my macro is now completed. Thanks a lot for your help. Appreciate a lot.