I have a database on Excel that is basically a compilation of three information from different sectors of the economy: Product Description, Product Code and Supplier. My aim is to extract from that worksheet (to another one) the information regarding only the sector that I need. Unfortunately, there is no filter whatsoever and the only way for me to identify the products that I want is by the Product Description, that has some keywords associated with the sector that I'm interested. I created a list of keywords that the Product Description might have and also a macro to go through this list and match those Product's Descriptions that contain it. After it matches something, it creates an entry on the new list that I'm building (to have only the products that I need).
The code is the one that follows:
Sub MacroName() Dim li_Line_1 As Integer Dim li_Line_2 As Integer Dim li_Line_3 As Integer li_Line_1 = 2 li_Line_2 = 3 li_Line_3 = 3 Do While Worksheets("WksInput").Cells(li_Line_1, 3) <> Empty Do While Worksheets("WksOutput").Cells(li_Line_2, 6) <> Empty Worksheets("WksOutput").Range("C" & li_Line_3).Select ActiveCell.Formula = "=SEARCH('WksOutput'!F" & li_Line_2 & ", 'WksInput'!E" & li_Line_1 & ")" If IsNumeric(ActiveCell) = True Then ActiveCell.Formula = "=CELL(""contents"",'WksInput'!E" & li_Line_1 & ")" Worksheets("WksOutput").Range("B" & li_Line_3).Select ActiveCell.Formula = "=CELL(""contents"",'WksInput'!C" & li_Line_1 & ")" Worksheets("WksOutput").Range("D" & li_Line_3).Select ActiveCell.Formula = "=CELL(""contents"",'WksInput'!B" & li_Line_1 & ")" li_Line_3 = li_Line_3 + 1 li_Line_1 = li_Line_1 + 1 Else li_Line_2 = li_Line_2 + 1 li_Line_3 = li_Line_3 End If Loop li_Line_2 = 3 li_Line_1 = li_Line_1 + 1 Loop End Sub
Line_1 = first line of the data base
Line_2 = first line of keywords' list
Line_3 = first line of the new list (with the products that I want)
WksInput = worksheet of the data base
WksOutput = worksheet of the new list
As you guys can see, it's an awful code (I have no experience at all at programming). It works, but it's a lot slow and it must have a simpler way of doing this. I used Do While because I'll be updating the database and the keywords' list regularly, so I thought it was a good idea for saving time on the future.
Could you guys help me? Any tips will be welcome. Also, I hope I've made myself clear. I can try detailing a bit more if needed.
Thanks a lot!!!