View Full Version : Search string or substring from range

07-27-2018, 02:42 AM
I have list of scientific names in Sheet 2, range A1:A100. In my textbox, if I enter input information such as "Gray wolf (Canis lupus) is native to North America and Eurasia.", I want to output "Gray wolf (<scientific_name>Canis lupus</scientific name>) is native to North America and Eurasia." assuming that Canis lupus is listed anywhere in A1:A100.

Any help would be greatly appreciated.

07-29-2018, 06:52 AM
try this:

Sub test()
Dim intxt As String

intxt = ActiveCell.Value
lb = InStr(intxt, "(")
rb = InStr(intxt, ")")
If lb > 0 And rb > 0 Then
sname = Mid(inxtx, lb, rb - lb)

With Worksheets("Sheet2")
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
listar = Range(Cells(1, 1), Cells(lastrow, 1))
For i = 2 To lastrow
If sname = listar(i, 1) Then
newstr = Replace(intxt, "(", "(<scientific_name>", 1, 1)
newstr = Replace(newstr, ")", "</scientific name>)", 1, 1)
Exit For
End If
Next i
End With
ActiveCell.Value = newstr
End If
End Sub