Find and Replace VBA script for a range with exact match with quotes

J

Jzaik

New Member
Joined
Jun 26, 2019
Messages
44
Hi there,

I am not familiar with VBA at all. I need to replace a whole set of alpha Xpath values that look like this:

ISOXML/Pmt/CrdtrAgt/CrdtrAgtTp/Nm
ISOXML/CasePmt/Agt/Nm

I have two columns set like this with the quotes to indicate that I want the whole string changed match for match.

Col1 Col 2
"Agt" "4XDD"
"Crdtr" "76ER"
"CrdtrAgt" "YY34"
"Pmt" "UU55"
"CasePmt" "788R"
"Nm" "91TR"

I used this generic script I found online:

Code: 
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Sub</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">MultiFindNReplace()</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb comments" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 130, 0) !important;">'Update 20140722</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Dim</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Rng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Dim</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range, ReplaceRng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">xTitleId = </code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"KutoolsforExcel"</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng = Application.Selection</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng = Application.InputBox(</code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"Original Range "</code><code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">, xTitleId, InputRng.Address, Type:=8)</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">ReplaceRng = Application.InputBox(</code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"Replace Range :"</code><code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">, xTitleId, Type:=8)</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">False</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">For</code> <code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Each</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Rng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">In</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">ReplaceRng.Columns(1).Cells</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb spaces" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">    </code><code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Next</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">True</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">End</code> <code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Sub</code>[/FONT][/COLOR]

The problem with this script is that

ISOXML/Pmt/CrdtrAgt/CrdtrAgtTp/Nm
ISOXML/CasePmt/Agt/Nm

Will be converted partly like

ISOXML/UU55/Crdtr4XDD/Crdtr4XDDTp/91TR
ISOXML/CaseUU55/4XDD/91TR


So I tried to modify the script like this:
Code: 
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Sub</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">MultiFindNReplace()</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb comments" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 130, 0) !important;">'Update 20140722</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Dim</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Rng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Dim</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range, ReplaceRng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">As</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Range</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">xTitleId = </code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"KutoolsforExcel"</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng = Application.Selection</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">InputRng = Application.InputBox(</code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"Original Range "</code><code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">, xTitleId, InputRng.Address, Type:=8)</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Set</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">ReplaceRng = Application.InputBox(</code><code class="vb string" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 0, 255) !important;">"Replace Range :"</code><code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">, xTitleId, Type:=8)</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">False</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">For</code> <code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Each</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Rng </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">In</code> <code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">ReplaceRng.Columns(1).Cells</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb spaces" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">    </code>[B]InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value & ""[/B][/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Next</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb plain" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-size: 1em !important; min-height: auto !important;">Application.ScreenUpdating = </code><code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">True</code>[/FONT][/COLOR]
[COLOR=#000000][FONT=Consolas]<code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">End</code> <code class="vb keyword" style="border-radius: 0px !important; background: none !important; border: 0px !important; bottom: auto !important; float: none !important; height: auto !important; left: auto !important; line-height: 1.1em !important; margin: 0px !important; outline: 0px !important; overflow: visible !important; padding: 0px !important; position: static !important; right: auto !important; top: auto !important; vertical-align: baseline !important; width: auto !important; box-sizing: content-box !important; font-family: Consolas, "Bitstream Vera Sans Mono", "Courier New", Courier, monospace !important; font-weight: bold !important; font-size: 1em !important; min-height: auto !important; color: rgb(0, 102, 153) !important;">Sub</code>[/FONT][/COLOR]

That doesn't seem to work.

If anyone knows a solution for this, it would be appreciated. Thanks.
 
Sort by date Sort by votes
Jzaik said:
Yeah, I still get the same error. It thinks this isn't defined:

Column_B_Dictionary As New Dictionary

Although one thing I noticed is that running the macro on an open workbook can be glitchy. I was trying to replace before and one item wouldn't replace. I couldn't figure out why. So I closed excel and restarted it. I ran it again, and it worked great.
Click to expand...
Try:
Code: 
Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
Column_B_C As Variant, Item As Variant, New_String As String, String_Match As Variant, Choice As Boolean, Column_B_Dictionary As Dictionary


Set Column_B_Dictionary = CreateObject("Scripting.Dictionary")
And you can combine the first two loops
Code: 
    For X = LBound(Column_B_C, 1) To UBound(Column_B_C, 1)




        For Y = 1 To 2 ' 1 and 2 correspond to the array column holding the items being searched for and their replacement




            Column_B_C(X, Y) = WorksheetFunction.Substitute(Column_B_C(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            Column_B_C(X, Y) = WorksheetFunction.Trim(Column_B_C(X, Y)) 'removes leading and trailing spaces from all items




        Next Y
            Column_B_Dictionary.Add Column_B_C(X, 1), Column_B_C(X, 2) 'items in B are used as a key while Column C is their item
    Next X
 
Last edited:
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
I put the code all in one below. When I was playing around with all the available options after 'As'---the dropdown didn't offer 'Dictionary' as an option. So I was playing around with it and I couldn't get very far.

I found this on some vba site but I'm out of my league with this.

To construct an instance of a dictionary object, just use the following lines of code:
<code class="language-vbnet" style="box-sizing: border-box; font-size: inherit; border: 0px; border-radius: 0px; background-color: transparent; padding: 0px; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: inherit; white-space: pre-wrap;">Dim objDictionary
Set objDictionary = CreateObject("Scripting.Dictionary")</code>
Code: 
 Sub Replace()Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
Column_B_C As Variant, Item As Variant, New_String As String, String_Match As Variant, Choice As Boolean, [B]Column_B_Dictionary As[/B]


Set Column_B_Dictionary = CreateObject("Scripting.Dictionary")




With ActiveSheet
    
    T = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    FindN_Replace_Array = .Range("A1:A" & T).Value2 'from the top row to the last used row in columns A & C
    
    T = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    Column_B_C = .Range("B1:C" & T).Value2








End With
    
  For X = LBound(Column_B_C, 1) To UBound(Column_B_C, 1)








        For Y = 1 To 2 ' 1 and 2 correspond to the array column holding the items being searched for and their replacement








            Column_B_C(X, Y) = WorksheetFunction.Substitute(Column_B_C(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            Column_B_C(X, Y) = WorksheetFunction.Trim(Column_B_C(X, Y)) 'removes leading and trailing spaces from all items








        Next Y
            Column_B_Dictionary.Add Column_B_C(X, 1), Column_B_C(X, 2) 'items in B are used as a key while Column C is their item
    Next X
        
    For X = LBound(Column_B_C, 1) To UBound(Column_B_C, 1)
    
        Column_B_Dictionary.Add Column_B_C(X, 1), Column_B_C(X, 2)
        
        'items in B are used as a key while Column C is their item
        
    Next X




    If MsgBox("Do you want to replace all possible substring matches that are not do you want to remove matches that are possibly part of another searchable substring", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True




    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom
        
        String_Match = Split(FindN_Replace_Array(X, 1), "/") 'creates delimited array
        
        For Each element In String_Match
  
                'T = Application.WorksheetFunction.Match(element, Column_B, 0) 'location of the replacement row within column C if it exists
            
            For Y = LBound(String_Match) To UBound(String_Match)
                
                If Column_B_Dictionary.Exists(String_Match(Y)) Then
                
                    'looks for {EXACT matches} for the array element
                    
                    String_Match(Y) = Column_B_Dictionary.Item(element)
    
                    If Choice = True Then 'replaces every substring match within the substring
                        
                        For T = LBound(String_Match) To UBound(String_Match)
                            
                            String_Match(T) = WorksheetFunction.Substitute(String_Match(T), element, Column_B_Dictionary.Item(element))
                        
                        Next T
                    
                    End If
              
                End If
                
            Next Y
                          
        Next element








        FindN_Replace_Array(X, 1) = Join(String_Match, "/")








    Next X








    ActiveSheet.Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Value2 = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 1)








    Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic




End Sub
 
Upvote 0
Jzaik said:
I put the code all in one below. When I was playing around with all the available options after 'As'---the dropdown didn't offer 'Dictionary' as an option. So I was playing around with it and I couldn't get very far.

I found this on some vba site but I'm out of my league with this.

To construct an instance of a dictionary object, just use the following lines of code:
<code class="language-vbnet" style="box-sizing: border-box; font-size: inherit; border: 0px; border-radius: 0px; background-color: transparent; padding: 0px; font-family: Menlo, Monaco, Consolas, "Courier New", monospace; color: inherit; white-space: pre-wrap;">Dim objDictionary
Set objDictionary = CreateObject("Scripting.Dictionary")</code>
Click to expand...
Shouldn't be a problem. Just remove the As you left in at the top.
Also remove the following since you combined the loops.Otherwise you will get an error
Code: 
   For X = LBound(Column_B_C, 1) To UBound(Column_B_C, 1)
    
        Column_B_Dictionary.Add Column_B_C(X, 1), Column_B_C(X, 2)
        
        'items in B are used as a key while Column C is their item
    [COLOR=#333333]Next X[/COLOR]
 
Last edited:
Upvote 0
Did you mean like this?

Code: 
Sub Replace()Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
Column_B_C As Variant, Item As Variant, New_String As String, String_Match As Variant, Choice As Boolean, ObjectColumn_B_Dictionary
 
Set Column_B_Dictionary = CreateObject("Scripting.Dictionary")




With ActiveSheet
    
    T = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    FindN_Replace_Array = .Range("A1:A" & T).Value2 'from the top row to the last used row in columns A & C
    
    T = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    Column_B_C = .Range("B1:C" & T).Value2




End With
    
  
        For Y = 1 To 2 ' 1 and 2 correspond to the array column holding the items being searched for and their replacement




            Column_B_C(X, Y) = WorksheetFunction.Substitute(Column_B_C(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            Column_B_C(X, Y) = WorksheetFunction.Trim(Column_B_C(X, Y)) 'removes leading and trailing spaces from all items


        Next Y
            
   
        
    If MsgBox("Do you want to replace all possible substring matches that are not do you want to remove matches that are possibly part of another searchable substring", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom
        
        String_Match = Split(FindN_Replace_Array(X, 1), "/") 'creates delimited array
        
        For Each element In String_Match
  
                'T = Application.WorksheetFunction.Match(element, Column_B, 0) 'location of the replacement row within column C if it exists
            
            For Y = LBound(String_Match) To UBound(String_Match)
                
                If Column_B_Dictionary.Exists(String_Match(Y)) Then
                
                    'looks for {EXACT matches} for the array element
                    
                    String_Match(Y) = Column_B_Dictionary.Item(element)
    
                    If Choice = True Then 'replaces every substring match within the substring
                        
                        For T = LBound(String_Match) To UBound(String_Match)
                            
                            String_Match(T) = WorksheetFunction.Substitute(String_Match(T), element, Column_B_Dictionary.Item(element))
                        
                        Next T
                    
                    End If
              
                End If
                
            Next Y
                          
        Next element


      FindN_Replace_Array(X, 1) = Join(String_Match, "/")


         Next X




    ActiveSheet.Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Value2 = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 1)




    Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic


End Sub
 
Upvote 0
Jzaik said:
Did you mean like this?
Click to expand...

Code: 
Sub Replace()Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
Column_B_C As Variant, Item As Variant, New_String As String, String_Match As Variant, Choice As Boolean, Column_B_Dictionary As Scripting.Dictionary
  
Set Column_B_Dictionary=New Scripting.Dictionary


With ActiveSheet
    
    T = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    FindN_Replace_Array = .Range("A1:A" & T).Value2 'from the top row to the last used row in columns A & C
    
    T = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    Column_B_C = .Range("B1:C" & T).Value2








End With
    
  
        For Y = 1 To 2 ' 1 and 2 correspond to the array column holding the items being searched for and their replacement








            Column_B_C(X, Y) = WorksheetFunction.Substitute(Column_B_C(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            Column_B_C(X, Y) = WorksheetFunction.Trim(Column_B_C(X, Y)) 'removes leading and trailing spaces from all items




        Next Y
            
   
        
    If MsgBox("Do you want to replace all possible substring matches that are not do you want to remove matches that are possibly part of another searchable substring", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True




    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom
        
        String_Match = Split(FindN_Replace_Array(X, 1), "/") 'creates delimited array
        
        For Each element In String_Match
  
                'T = Application.WorksheetFunction.Match(element, Column_B, 0) 'location of the replacement row within column C if it exists
            
            For Y = LBound(String_Match) To UBound(String_Match)
                
                If Column_B_Dictionary.Exists(String_Match(Y)) Then
                
                    'looks for {EXACT matches} for the array element
                    
                    String_Match(Y) = Column_B_Dictionary.Item(element)
    
                    If Choice = True Then 'replaces every substring match within the substring
                        
                        For T = LBound(String_Match) To UBound(String_Match)
                            
                            String_Match(T) = WorksheetFunction.Substitute(String_Match(T), element, Column_B_Dictionary.Item(element))
                        
                        Next T
                    
                    End If
              
                End If
                
            Next Y
                          
        Next element




      FindN_Replace_Array(X, 1) = Join(String_Match, "/")




         Next X








    ActiveSheet.Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Value2 = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 1)








    Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic




End Sub
 
Last edited:
Upvote 0
I tried a few different changes and it doesn't like the new dictionary. Then when I finally tried create object like below, it says subscript out of range. That's fine though. I'll use the original script, that should be good enough. Thanks for everything.

Code: 
Sub Replace()
Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
Column_B_C As Variant, Item As Variant, New_String As String, String_Match As Variant, Choice As Boolean, Column_B_Dictionary As Object
Set Column_B_Dictionary = CreateObject("Scripting.Dictionary")
  
With ActiveSheet
    
    T = .Cells(.Rows.Count, "A").End(xlUp).Row
    
    FindN_Replace_Array = .Range("A1:A" & T).Value2 'from the top row to the last used row in columns A & C
    
    T = .Cells(.Rows.Count, "B").End(xlUp).Row
    
    Column_B_C = .Range("B1:C" & T).Value2




End With
    
  
        For Y = 1 To 2 ' 1 and 2 correspond to the array column holding the items being searched for and their replacement


            Column_B_C(X, Y) = WorksheetFunction.Substitute(Column_B_C(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            Column_B_C(X, Y) = WorksheetFunction.Trim(Column_B_C(X, Y)) 'removes leading and trailing spaces from all items




        Next Y
            
   
        
    If MsgBox("Do you want to replace all possible substring matches that are not do you want to remove matches that are possibly part of another searchable substring", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True




    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom
        
        String_Match = Split(FindN_Replace_Array(X, 1), "/") 'creates delimited array
        
        For Each element In String_Match
  
                'T = Application.WorksheetFunction.Match(element, Column_B, 0) 'location of the replacement row within column C if it exists
            
            For Y = LBound(String_Match) To UBound(String_Match)
                
                If Column_B_Dictionary.Exists(String_Match(Y)) Then
                
                    'looks for {EXACT matches} for the array element
                    
                    String_Match(Y) = Column_B_Dictionary.Item(element)
    
                    If Choice = True Then 'replaces every substring match within the substring
                        
                        For T = LBound(String_Match) To UBound(String_Match)
                            
                            String_Match(T) = WorksheetFunction.Substitute(String_Match(T), element, Column_B_Dictionary.Item(element))
                        
                        Next T
                    
                    End If
              
                End If
                
            Next Y
                          
        Next element


    FindN_Replace_Array(X, 1) = Join(String_Match, "/")


    Next X




    ActiveSheet.Range("A1:A" & ActiveSheet.UsedRange.Rows.Count).Value2 = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 1)
    Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic




End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA for Advanced filter copy to another location
2
Replies
14
Views
2K
Fluff
Fluff
I
  • Question Question
Copy files from one folder to another based on a list in Excel
Replies
1
Views
2K
Fluff
Fluff
P
  • Question Question
Advice with VBA Code to Move Files
Replies
3
Views
1K
Fluff
Fluff
A
  • Question Question
Excel Date Time Stamp VBA
Replies
3
Views
984
DanteAmor
DanteAmor
rediffusion
  • Question Question
How to pull data from a Sheet to create a Context Menu? How to combine two VBA codes?
Replies
0
Views
998
rediffusion
rediffusion

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top