• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search in table columns

Belleke

Well-Known Member
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.
 

Attachments

Marc L

Excel Ninja
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

Well-Known Member
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

Excel Ninja
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 :​
Code:
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 !​
 

Marc L

Excel Ninja
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 …​
 
Top