CODEWITHSUNDEEP
excelvba
Jason
Jason

Reputation: 13

Search a column for words in a table on another sheet and insert the value from the cell next to it in the current cell

I want to:

I know an if then else formula is listed to 7 items, and im working with a list longer that that (and growing).

Upvotes: 1

Views: 1258

Answers (1)

Florent B.
Florent B.

Reputation: 42518

An example to replace in all the sheets all the expressions defined in a range:

Sub ReplaceMulti()
  Dim dictionary As Range, sh As Worksheet, data(), r&, search$, replace$

  ' Loads a translation table from a worksheet
  ' The first column is the searched string and the second is the replacement
  Set dictionary = [Sheet1!A1:B10]
  data = dictionary.value

  ' iterate each sheet
  For Each sh In ActiveWorkbook.Worksheets

    ' skip the sheet with the dictionary
    If sh.CodeName <> dictionary.Worksheet.CodeName Then

      ' iterate each expression
      For r = 1 To UBound(data)

        If Not IsEmpty(data(r, 1)) Then
          search = data(r, 1)
          replace = data(r, 2)

          ' replace in the sheet
          sh.Cells.replace What:=search, replacement:=replace, _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False

        End If
      Next
    End If
  Next
End Sub

Upvotes: 0

Related Questions