Consulting

Results 1 to 2 of 2

Thread: Search string or substring from range

  1. #1

    Search string or substring from range

    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.

  2. #2
    VBAX Expert
    Joined
    May 2016
    Posts
    604
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •