PDA

View Full Version : VBA Find & Replace based on cell reference within a range



GJones7
03-25-2017, 11:11 AM
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
03-25-2017, 11:24 PM
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
03-26-2017, 04:20 AM
Cross-Post at this link
http://www.ozgrid.com/forum/showthread.php?t=203481

GJones7
03-26-2017, 08:57 AM
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
03-26-2017, 09:23 PM
Cross posting is uncomfortable.
First of all I hope you will defuse this situation.


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