How optimize this code?

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:


VBA 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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,536
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
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
 
Solution

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
 

F_Ribeiro

New Member
Joined
Jan 14, 2021
Messages
28
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,536
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,500
Messages
5,636,690
Members
416,935
Latest member
Atulcp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top