How optimize this code?

[F_Ribeiro]

Hello guys!

A colleague sent me this code yesterday, the purpose of which is to find and replace values in columns after checking the contents of a cell in a simple but efficient way. And then I would like to know how I can take the opportunity to look for other words in the same If, but substituting with the same values in the columns of rowNumber. Ex: in addition to "sugar" how a essencial item on a recipe, I would like to look for "condensed milk" and "granulated chocolate" in the same condition with "OR" as logic operator. Is it possible using this same stretch or will I have to use Else If? Here is the code:

Sub essential_in_recipe () 
Application.ScreenUpdating = False
         
Dim W as spreadsheet     
Dim rowNumber while long
           
Set W = Sheets ("Sheet1")      
W.Range ("D1"). Select
           
last_line = Selection.End (xlDown) .Row         

For rowNumber = 2 for last_line             
If InStr (1, LCase (Range ("D" & rowNumber)), "sugar")> 0 Then
                     
Range ("B" & rowNumber) .Value = "Brigadeiro / Chocolate Cake"             
Range ("A" & rowNumber) .Value = "Dessert"    
             
End If
     
Next  
   
Application.ScreenUpdating = True 
End Sub

And of course, if there is any suggestion on how to do this procedure better, I am willing to learn, he he!

 

[Fluff]

How about

Sub FRibeiro()
   Dim Cl As Range
   Dim Ws As Worksheet
           
   Set W = Sheets("Sheet1")
   For Each Cl In Ws.Range("D2", Ws.Range("D" & Rows.Count).End(xlUp))
      If InStr(1, Cl, "sugar", vbTextCompare) > 0 Or InStr(1, Cl, "condensed milk", vbTextCompare) > 0 Or InStr(1, Cl, "granulated chocolate", vbTextCompare) > 0 Then
         Cl.Offset(, -2).Value = "Brigadeiro / Chocolate Cake"
         Cl.Offset(, -3).Value = "Dessert"
      End If
   Next Cl
End Sub

 

[F_Ribeiro]

I am grateful once again to you for responding, he he! And sorry for the delay.

Could you ask me some questions? The InStr method, what does it do and how does it work?

And I also noticed that you used vbtextcompare. Correct me if I'm wrong, but does this method look for a word regardless of whether the characters are uppercase or lowercase? I ask this because my colleague used LCase and had to enter a string with lowercase characters in the search.

 

[Fluff]

And I also noticed that you used vbtextcompare. Correct me if I'm wrong, but does this method look for a word regardless of whether the characters are uppercase or lowercase?

That's right. IMO far easier than using LCase.
For more on InStr have a look here VBA InStr Function | How to Use Excel VBA InStr Function?

 

[F_Ribeiro]

Ok, ha ha! This was of great help, I had a beautiful learning insight and syntax of my code thanks to you.

If I travel to USA someday, remind me send a huge box of brigadeiros to you, KKKKKKKKKKKKKKKKKKKKKK!

 

[Fluff]

You're welcome & thanks for the feedback.