Substitute error

[ekslit]

Hello Guys,

I m trying to get this forumla below working but i keep getting this error: Unable to get the Substitute property of the WorksheetFunction class

 Range("M" & cell.Row).Value = Right(cell.Value, Len(cell.Value) - Application.WorksheetFunction.Find("@", Application.WorksheetFunction.Substitute(cell.Value, "-", "@", Len(cell.Value) - Len(Application.WorksheetFunction.Substitute(cell.Value, "-", ""))), 1))

In excel, everything working fine with this formula:

=RIGHT(D4, LEN(D4) - FIND("@", SUBSTITUTE(D4, "-", "@", LEN(D4) - LEN(SUBSTITUTE(D4, "-", ""))), 1))

Can someone help me with this please?

Thanks.

 

[Fluff]

That works for me, but you could simplify it like

Range("M" & cell.Row).Value = Right(cell, Len(cell) - InStrRev(cell, "-"))

 

[ekslit]

Thanks @Fluff ,

Your formula is working perfectly and indeed, much simple than mine

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ekslit]

Your welcome.

Last question, i m trying to do Index / match with several criteria but the formula is not working.

=INDEX(PointageStructure!$G$2:$G$1000,MATCH(1,(D2=PointageStructure!$B$2:$B$1000)*("Sub-task"=PointageStructure!$C$2:$C$1000)*("Analyste"=PointageStructure!$M$2:$M$1000),0))

Do you have any clue about the issue?

thanks again for your help.

 

[Fluff]

As that is a totally different question, it needs a new thread. Thanks