VBA Find & Replace based on cell reference within a range

[GJones7]

This is my first time on the site and hopefully someone can help!

I have specific text in column D that I want to look-up replace with text from column B if the value in column D is equal to column C, but within a range of rows sharing a the same cell reference in column A

The text to be replaced will always be within the symbol § and not fixed to 4 characters, they are part of equations so other text needs to be ignored.

Also if it couldn't find a match I would need this easily identifiable, i.e. replace the text with #N/A instead, so it can be investigated,

Before

A	B	C	D	E
1	Ref.1	10	AAAA
2	Ref.1	20	BBBB
3	Ref.1	30	CCCC
4	Ref.1	40	DDDD	(§CCCC§*§AAAA§*10)/(§BBBB§-§EEEE§)
5	Ref.1	50	EEEE
6	Ref.2	110	AAAA
7	Ref.2	120	BBBB	(§AAAA§ - §DDDD§) * 1000 * 0.5 /(10*5)
8	Ref.2	130	CCCC
9	Ref.2	140	DDDD

After

A	B	C	D	E
1	Ref.1	10	AAAA
2	Ref.1	20	BBBB
3	Ref.1	30	CCCC
4	Ref.1	40	DDDD	(30*10*10)/(20-#N/A)
5	Ref.1	50	EEEE
6	Ref.2	110	AAAA
7	Ref.2	120	BBBB	(110 - 140) * 1000 * 0.5 /(10*5)
8	Ref.2	130	CCCC
9	Ref.2	140	DDDD

[mana]

Option Explicit


Sub test()
    Dim dic As Object
    Dim re As Object
    Dim v
    Dim i As Long, s As String
    Dim k As Long
    Dim m
    Dim r As String
    
    Set dic = CreateObject("scripting.dictionary")
    
   With Cells(1).CurrentRegion
        For i = 1 To .Rows.Count
            s = .Cells(i, 2).Value
            If Not dic.exists(s) Then Set dic(s) = CreateObject("scripting.dictionary")
            dic(s)(.Cells(i, 4).Value) = .Cells(i, 3).Value
        Next
        
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = "§(.*?)§"
        re.Global = True
        
        For i = 1 To .Rows.Count
            s = .Cells(i, 5).Value
            If s <> "" Then
                r = .Cells(i, 2).Value
                For Each m In re.Execute(s)
                    If dic(r).exists(m.SubMatches(0)) Then
                        s = Replace(s, m, dic(r)(m.SubMatches(0)))
                    Else
                        s = Replace(s, m, "#N/A")
                    End If
            Next
            .Cells(i, 5).Value= s
            End If
        Next
    End With
    
End Sub

[YasserKhalil]

Cross-Post at this link
http://www.ozgrid.com/forum/showthread.php?t=203481

[GJones7]

Thanks Mana, that worked great. Just one more question, can it be made not to be case sensitive between the find and replace values?

[mana]

Cross posting is uncomfortable.
First of all I hope you will defuse this situation.


----------------------------------