Turn Formula in Evaluate Function

[Adeel1]

Hi All

Hi All

below formula is working fine but when i try to put in code this is not working i can do with loop but i want to get without loop does this possible, please help

=IFERROR(MID(INDEX($A$3:$A$17,AGGREGATE(15,6,ROW($A$3:$A$17)-2/((--MID($A$3:$A$17,6,2)=65)+(--MID($A$3:$A$17,6,2)=66)),ROW(INDIRECT("1:"&SUM((--MID($A$3:$A$17,6,2)=65)+(--MID($A$3:$A$17,6,2)=66)))))),6,255),"")

Cells(3, "c") = Evaluate("IFERROR(MID(INDEX($A$3:$A$" & p & ",AGGREGATE(15,6,ROW($A$3:$A$" & p & ")-2/((--MID($A$3:$A$" & p & ",6,2)=65)+(--MID($A$3:$A$" & p & ",6,2)=66)),ROW(INDIRECT(""1:""&SUM((--MID($A$3:$A$" & p & ",6,2)=65)+(--MID($A$3:$A$" & p & ",6,2)=66)))))),6,255),"""")")

Turn Formula in Evaluate Function

Hi All below formula is working fine but when i try to put in code this is not working i can do with loop but i want to get without loop does this possible, please help...

www.excelforum.com

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Adeel1]

thnx , i have 365, i updated as you suggested

 

[Fluff]

Thanks for that.
As you are on 365, why not use the filter function like

Sub Adeel()
   Dim x As Variant
   
   With Range("A3", Range("A" & Rows.Count).End(xlUp))
      x = Evaluate(Replace("FILTER(MID(@,6,255),(MID(@,6,2)=""65"")+(MID(@,6,2)=""66""))", "@", .Address))
   End With
   If Not IsError(x) Then Cells(3, 3).Resize(UBound(x)).Value = x
End Sub

 

[Adeel1]

thnx sir for help, some questions normal formula will be like =FILTER(MID(A3:A17,6,255),(--MID(A3:A17,6,2)=65)+(--MID(A3:A17,6,2)=66)) as per solution suggested you
what is @ in mid function(in code) what it will return, please

Adeel

 

[Fluff]

The @ is replaced with the .Address, just a shorthand way of doing it.

 

[Adeel1]

Thank you sir for your help!

 

[Fluff]

You're welcome & thanks for the feedback.