Macro code - check a column for certain text strings and place corresponding text in the next column RRS feed

  • Question

  • I'd like find the macro code that will let me check each row of a column for multiple text strings.  If it matches a particular text string it will add the corresponding text in the same row but in the next column.  For example:

    If the text in F2 = "Group A" then put the word "Vocabulary" in G2.

    If the text in F2 = "Group B" then put the word "Watch" in G2

    I need it to go through 22 possible text strings for each row in column F.  There's also a possibility that there will be no text in the F column.  In which case nothing gets put in the G column.

    What do you think?  Can you help me out?

    I appreciate it!

    Thursday, November 9, 2017 6:55 PM

All replies

  • Hi Claytronik,

    You could put the the particular text string and its corresponding text in a dictionary and then iterate through cells in column F. If the dictionary keys contains any value in the cell, we could get its corresponding text from the dictionary and then put it in the next column.

    Here is the example.

    Sub Macro1()
    'Need add reference to Microsoft Scripting Runtime
    Dim dict As Scripting.Dictionary
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "Group A", "Vocabulary"
    dict.Add "Group B", "Watch"
    dict.Add "Group C", "Test"
    Application.ScreenUpdating = False
    'get columnF's last Row index
    lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 6).End(xlUp).Row
    'iterate through row 2 to last row in ColumnF
    For i = 2 To lastRow
    If dict.Exists(ActiveSheet.Cells(i, 6).Value) Then
    ActiveSheet.Cells(i, 7).Value = dict(ActiveSheet.Cells(i, 6).Value)
    End If
    Next i
    Application.ScreenUpdating = True
    End Sub

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Friday, November 10, 2017 1:36 AM