CODEWITHSUNDEEP
excelvba
Ilya_Kuvshi
Ilya_Kuvshi

Reputation: 51

Find specific text value in column and return it to its own row to another cell

I'd like to write an Excel macro (CommandButton) to search for specific words in a specific column and return the specific words to its same row but another cell.
For example: I want to find "chocolate", "muffin", "lemon", "monkey", "baby" in Column(A) and when it finds these specific words return them to Column(D) to its own row where it was found.

Template

I want the macro to search for only these 5 words in the entire column(A:A) and when it finds it return the specific word(s) to its own row where it was found and insert it to another cell but the same row (for example "chocolate" to "D3" cell with the score "4" in "B3" to "E3").

How and what functions should I use to make it work with I click on CommandButton?

Upvotes: 0

Views: 625

Answers (1)

Skippy
Skippy

Reputation: 1590

Try the following code:

Dim intSentenceRow As Integer
Dim intWordRow As Integer
Dim strSentence As String
Dim strWord As String

intSentenceRow = 3
strSentence = Range("A" & intSentenceRow).Value

Do
    intWordRow = 3
    strWord = Range("G" & intWordRow).Value

    Do
        If InStr(strSentence, strWord) > 0 Then
            Range("D" & intSentenceRow).Value = strWord
            Range("E" & intSentenceRow).Value = Range("B" & intSentenceRow).Value
            Exit Do
        End If
        intWordRow = intWordRow + 1
        strWord = Range("G" & intWordRow).Value
    Loop Until strWord & "" = ""

    intSentenceRow = intSentenceRow + 1
    strSentence = Range("A" & intSentenceRow).Value

Loop Until strSentence & "" = ""

This assumes that your sentences run in a contiguous list (no blank cells in the middle of the list). It also assumes (because this is what you said) that you want to copy the score associated with the relevant sentence into column E. Why have you also listed scores against the specific words? If you wanted instead to copy the score associated with the found word (rather than the score associated with the sentence in which the word was found) you would replace the assignment of the "E" column values with:

Range("E" & intSentenceRow).Value = Range("H" & intWordRow).Value

Upvotes: 1

Related Questions