Search in table columns

[Belleke]

Hi,
I am looking for code to solve my problem in attached example.
I put the explanation in the file.
Please advice

Thanks in advance.

 

[Marc L]

Hi,​

​

as the data are located in an Excel table, why don't you use an obvious easy formula ?​

As even if a VBA procedure is necessary the same formula can be used …​

​

 

[Belleke]

I tryed that, but there are more than 100.000 rows in the file. The amount of formulas slows down the file extremely.
I put my question in VBA, i am hoping for a vba solution,not a formula suggestion.

 

[Marc L]

So you did not understand or just misread and maybe you do not know even under VBA​

some Excel features can be more efficient, faster and easier to maintain than some bad classic looping code !​

​

So according to your attachment a VBA demonstration as a beginner starter :​

Sub Demo1()
         Const F = "IF((A2:A#=""Yes"")*(B2:B#=C2:C#),""Processed"","""")"
    With SH1.[A1].CurrentRegion.Rows
        .Range("D2:D" & .Count).Value2 = .Parent.Evaluate(Replace(F, "#", .Count))
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Belleke]

Thank you,problem solved

 

[Marc L]

There is also the classic Range.Find method - to see in VBA help - : as this is a cell by cell process​

so the less "Processed" cells to update, the faster and obviously the more, the slower …​

​

Another way is to combine a filter or an advanced filter with the SpecialCells function in case Demo1 is too long …​

​