View Full Version : Change code to include range of cells

08-08-2018, 04:53 AM
Is there a way to modify this code in such a way that it searches not a fixed search term, but search terms found in another sheet, say Sheet 2 Range A2:A1000 or last row? What the existing macro does is when it sees a match in the search term, it modifies the value in the textbox (encloses the term in SN tags), Any help can be appreciated. Honestly, I don't know if I'm doing this correctly. I am just assuming the only a single occurrence of the search term is found (what if multiple occurrences?)

Dim wholeWord As String
Dim searchTerm As String

wholeWord = Sheets("Sheet1").TextBox1.Text
searchTerm = "Murraya koenigii"

MsgBox Left(wholeWord, InStr(LCase(wholeWord), LCase(searchTerm)) - 1) & "<SN>" & Mid(wholeWord, InStr(LCase(wholeWord), LCase(searchTerm)), Len(searchTerm)) & "</SN>" & " " & Mid(wholeWord, InStr(LCase(wholeWord), LCase(searchTerm)) + Len(searchTerm) + 1, Len(wholeWord))