Hello dawoodmpm.
This is has been tested using the examples you provided. However, I cannot guarantee it will work with your actual data since it has been tested on such a limited data set.
Run this macro on a copy of your workbook before installing it in your main workbook. Let me know what your results are.
' Thread: http://www.vbaexpress.com/forum/showthread.php?64251-Find-and-Replace-Multiple-values-in-Excel
' Poster: dawoodmpm
' Written: December 16, 2018
' Author: Leith Ross
Sub Macro1()
Dim Cell As Range
Dim Data As Variant
Dim Index As Long
Dim Matches As Object
Dim NewTag As Variant
Dim OldTag As Variant
Dim RegExp As Object
Dim Rng As Range
Dim RngBeg As Range
Dim RngEnd As Range
Dim Tag As Variant
Dim Tags As Object
Dim Text As String
Dim Wks As Worksheet
Set Wks = ThisWorkbook.Worksheets("Room Tags")
Set RngBeg = Wks.Range("A2")
Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
Set Rng = Wks.Range(RngBeg, RngEnd)
Set RegExp = CreateObject("VBScript.RegExp")
RegExp.Global = True
RegExp.MultiLine = True
RegExp.Pattern = "\(-?\d+\.\w+\)"
Set Tags = CreateObject("Scripting.Dictionary")
Tags.CompareMode = vbTextCompare
For Each Cell In Rng
OldTag = Trim(Cell)
NewTag = Trim(Cell.Offset(0, 1))
If OldTag <> "" Then
If Not Tags.Exists(OldTag) Then
Tags.Add OldTag, NewTag
End If
End If
Next Cell
For Each Wks In ThisWorkbook.Worksheets
If UCase(Wks.Name) <> "ROOM TAGS" Then
Set RngBeg = Wks.Range("H11")
Set RngEnd = Wks.Cells(Rows.Count, RngBeg.Column).End(xlUp)
Set Rng = Wks.Range(RngBeg, RngEnd)
If RngEnd.Row >= RngBeg.Row Then
If Rng.Cells.Count = 1 Then
ReDim Data(1, 1)
Data(1, 1) = Rng.Value
Else
Data = Rng.Value
End If
For Index = 1 To UBound(Data, 1)
Text = Data(Index, 1)
Set Matches = RegExp.Execute(Text)
For Each OldTag In Matches
NewTag = Tags(OldTag)
Text = Replace(Text, OldTag, NewTag, OldTag.FirstIndex + 1, 1, vbTextCompare)
Next OldTag
Data(Index, 1) = Text
Next Index
Rng.Value = Data
End If
End If
Next Wks
End Sub