Hi everyone,
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
Display More
Where:
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!!!