CODEWITHSUNDEEP
vbaexcel
cmc
cmc

Reputation: 27

How do I add keywords to a category in Excel using VBA?

I have a sheet with various words which I'm using as keywords. In the first row of each column I have a title. What I'd like to do is assign each word in the subsequent rows to it's corresponding row 1 category.

+---+------------+-------------+
|   |     A      |      B      |
+---+------------+-------------+
| 1 | Category1  | Category2   |
| 2 | Keyword1a  | Keyword2a   |
| 3 | Keyword1b  | Keyword2b   |
+---+------------+-------------+

Basically I want to "assign" Category1 to Keywords 1a to 1x and so on. How can I accomplish this in VBA? I have already defined the categories and keywords in collections, I just want to link the two.

EDIT: The reason I want to use VBA is because I am already using VBA to pick out keywords in cells. I would like to use the keywords that exist in a certain string to match them against the category name and output the category (right now it only outputs the keywords present).

Upvotes: 0

Views: 280

Answers (1)

QHarr
QHarr

Reputation: 84465

Using a dictionary. Keywords are the keys, categories are the values. Loop the test data checking with Instr to see if the key is found in the test data string. If it is found then use the key to output the associated dictionary value. Easy enough with the principle demonstrated below to add another loop over the data range containing the test data.

Option Explicit
Private Sub RunningSheetClick_Click()
    Dim dict As Object, arr(), i As Long, key As Variant
    Set dict = CreateObject("Scripting.Dictionary")
    With ActiveSheet
        arr = Intersect(.Columns("A:B"), .UsedRange).Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            dict(arr(i, 1)) = arr(i, 2)
        Next i
    End With
    For Each key In dict.keys
         If InStr(key, [D2].Value) > 0 Then [E2].Value = dict(key)
    Next key
End Sub

Data

Upvotes: 2

Related Questions