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:
this second code didn't do the trick. It jumbled a bunch of things.

200002/10090104/1041/ClrSysMmb1047/ClrSys1047

But that's fine the other one worked great.

I'm not sure if you can PM me on here, but if you want to forward me an email address, I can zelle you over something for your trouble. I sincerely appreciate you constructing this for me.
Click to expand...
I double checked the last elseif statement and found that I forgot a 1, inside the instr()
Code: 
Sub Replace_Items()


Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual:


Dim FindN_Replace_Array() As Variant, X As Long, H As Long, element2 As Variant, element As Variant, Column_B() As Variant, T As Long, G As Long


FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).value2 'from the top row ro the last used row in columns A & C


Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array


For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)
         
        For y = 2 To 3 ' 2 and 3 correspond to the array column holding the items being searched for and their replacement
        
            FindN_Replace_Array(X, y) = Replace(FindN_Replace_Array(X, y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, y) = Trim(FindN_Replace_Array(X, y)) 'removes leading and trailing spacesfrom all items
        
        Next y
    
Next X


For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)
    
        For Each element In Column_B
        
                If IsEmpty(element) = False Then 'if the element is not empty
 
                   If UBound(Split(FindN_Replace_Array(X, 1), element)) > 0 Then 'if element is in the original string
                    
                        T = Application.WorksheetFunction.Match(element, Column_B, 0) 'finds the index number of what is being searched for [B]
                                                                                    'since this number correlates with an item in column C this number is used to find the replacement item in the arrat
                        
                        G = UBound(Split(FindN_Replace_Array(X, 1), element)) 'count of how many time the specified element appears in the string
                        
                        Do While G <> 0 'until there are no more matches for the item
                                                                                        'chr(47) is a /
                            If InStr(1, FindN_Replace_Array(X, 1), element & Chr(47)) = 1 Then 'searches for A/ in the first position
                                   
                                FindN_Replace_Array(X, 1) = Replace(FindN_Replace_Array(X, 1), element & Chr(47), FindN_Replace_Array(T, 3) & Chr(47))
                            
                            ElseIf InStr(1, FindN_Replace_Array(X, 1), Chr(47) & element & Chr(47)) <> 0 Then 'searches for /A/
                                    
                                FindN_Replace_Array(X, 1) = Replace(FindN_Replace_Array(X, 1), Chr(47) & element & Chr(47), Chr(47) & FindN_Replace_Array(T, 3) & Chr(47))
                            
                            ElseIf InStrRev(FindN_Replace_Array(X, 1), Chr(47) & element) = Len(FindN_Replace_Array(X, 1)) - Len(element) Then 'searches for /A in the last in the last position
                                    
                                FindN_Replace_Array(X, 1) = Replace(FindN_Replace_Array(X, 1), Chr(47) & element, Chr(47) & FindN_Replace_Array(T, 3))
                            
                            ElseIf InStr(1, FindN_Replace_Array(X, 1), element) <> 0 Then 'replace every instance of the element within the original string
                            
                                FindN_Replace_Array(X, 1) = Replace(FindN_Replace_Array(X, 1), element, FindN_Replace_Array(T, 3))
                                
                                Exit Do
                            
                            End If
                            
                            G = G - 1 'THE ABOVE BLOCK OF CODE WILL REMOVE 1 ITEM SO SUBTRACT 1 FROM THE TOTAL NUMBER OF TIMES THE ELEMENT APPEARS IN TEH STRING
                            
                        Loop 'Loops again if a match is still within the string
                    
                    End If
                
                End If
        
        Next element
        
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

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
MoshiM said:
I double checked the last elseif statement and found that I forgot a 1, inside the instr()
Click to expand...


Another version:
Code: 
Sub Replace_Items()


Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual:


Dim FindN_Replace_Array() As Variant, X As Long, H As Long, element2 As Variant, element As Variant, Column_B() As Variant, T As Long, G As Long, _
Q As Long, Item As Variant, My_C As New Dictionary, New_S As String, New_A() As Variant


Set My_C = CreateObject("Scripting.Dictionary")


FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).value2 'from the top row ro the last used row in columns A & C


Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array


For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)
         
        For y = 2 To 3 ' 2 and 3 correspond to the array column holding the items being searched for and their replacement
        
            FindN_Replace_Array(X, y) = Replace(FindN_Replace_Array(X, y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, y) = Trim(FindN_Replace_Array(X, y)) 'removes leading and trailing spaces from all items
        
        Next y
    
Next X


For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)
    
    My_C.Add X, Split(FindN_Replace_Array(X, 1), Chr(47)) 'string is split with "/" and each item is added to an array
        
        For Each element In Column_B
        
                If IsEmpty(element) = False And InStr(1, FindN_Replace_Array(X, 1), element) Then
                    'if the element is not empty and it exists within the string


                    T = Application.WorksheetFunction.Match(element, Column_B, 0) 'location of the replacement row within column C
                    
                    If Find_N_Replace(X, 1) = element Then 'if searched string is an exact match for something in B
                    
                        Find_N_Replace(X, 1) = Find_N_Replace(T, 3) 'if the item itself is an exact match for the element
                    
                        Else 'Otherwise
                    
                            If UBound(Split(FindN_Replace_Array(X, 1), Chr(47))) <> 0 Then 'delimit with a "/" and replace
                                                                    'if any  item in the array is an exact match for the item
                                 For Each Item In My_C.Items(X) '
                                         
                                     If element = Item Then 'if exact match then
                                             
                                           Item = Find_N_Replace(T, 3)
                                     
                                     End If
                                        
                                 Next Item
                            
                            End If
                        
                            Find_N_Replace(X, 1) = Join(My_C.Items(X), Chr(47)) 'puts elements back together with a "/" inbetween
                            
                            G = UBound(Split(Find_N_Replace(X, 1), element)) 'determines if there are still matches for the elements within the original string
                                                                           'ie: part of a substring
                            If G > 0 And InStr(1, Find_N_Replace(X, 1), "/") = 0 Then
                                        'check the last/first elements in the preceding and next items if there are no "/"
                                Do Until G = 0
                                        
                                    H = InStr(1, Find_N_Replace(X, 1), element) 'gets the position start of element if it is still in the array
                                    
                                    If (H <> 1 And H <> Len(Find_N_Replace(X, 1)) - Len(element)) And (Mid(Find_N_Replace(X, 1), H - 1, 1) <> "/" Or Mid(Find_N_Replace(X, 1), H + 1, 1) = "/") _ 
                                    And (Mid(Find_N_Replace(X, 1), H + 1, 1) <> "/" Or Mid(Find_N_Replace(X, 1), H + 1, 1) = "/") Then
                                    'first and last already searched ?
                                    'check to make sure that it is not xAx?
                                    'Do nothing if the above is true
                                    Else
                                    
                                        Find_N_Replace(X, 1) = Replace(Find_N_Replace(X, 1), element, Find_N_Replace(T, 3), H, 1)
                                    
                                    End If
                                    
                                    G = G - 1
                                     
                                Loop
                                
                            End If
                        
                    End If
                
                End If
                             
        Next element
        
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
It didn't like this:

Code: 
Dim FindN_Replace_Array() As Variant, X As Long, H As Long, element2 As Variant, element As Variant, Column_B() As Variant, T As Long, G As Long, _
Q As Long, Item As Variant, [B]My_C As New Dictionary[/B], New_S As String, New_A() As Variant
 
Upvote 0
Jzaik said:
It didn't like this:

Code: 
Dim FindN_Replace_Array() As Variant, X As Long, H As Long, element2 As Variant, element As Variant, Column_B() As Variant, T As Long, G As Long, _
Q As Long, Item As Variant, [B]My_C As New Dictionary[/B], New_S As String, New_A() As Variant
Click to expand...
Remove it and the comma that follows
 
Upvote 0
MoshiM said:
Remove it and the comma that follows
Click to expand...
Code: 
Sub Replace_Items()


    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
    Item As Variant, My_C As New Dictionary, New_String As String, String_Match() As Variant, Choice As Boolean


    FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).value2 'from the top row ro the last used row in columns A & C


    Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)


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


            FindN_Replace_Array(X, Y) = Replace(FindN_Replace_Array(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, Y) = Trim(FindN_Replace_Array(X, Y)) 'removes leading and trailing spaces from all items


        Next Y


    Next X


    If MsgBox("Do you want to replace all possible substring matches that are not [A],[A/],[/A/] or [/A]", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom


        My_C.Add X, Split(FindN_Replace_Array(X, 1), "/")  'Column A is split with "/" and each item is added to an array


        For Each element In Column_B


            On Error Resume Next


            T = Application.WorksheetFunction.Match(element, Column_B, 0)
            'location of the replacement row within column C if it exists
            String_Match = My_C.Item(X)


            For Y = 0 To UBound(String_Match)
                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(X, T))


            Next Y
            
            String_New = Join(String_Match, "/") 'joins the array back together again with a "/"


        Next element


        FindN_Replace_Array(X, 1) = String_New


        If Choice = True Then


            For Each Item In Column_B


                T = Application.WorksheetFunction.Match(Item, Column_B, 0)


                String_New = Replace(FindN_Replace_Array, Item, FindN_Replace_Array(T, 3))


            Next Item


        End If


    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
MoshiM said:
Code: 
Sub Replace_Items()


    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
    Item As Variant, My_C As New Dictionary, New_String As String, String_Match() As Variant, Choice As Boolean


    FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).value2 'from the top row ro the last used row in columns A & C


    Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)


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


            FindN_Replace_Array(X, Y) = Replace(FindN_Replace_Array(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, Y) = Trim(FindN_Replace_Array(X, Y)) 'removes leading and trailing spaces from all items


        Next Y


    Next X


    If MsgBox("Do you want to replace all possible substring matches that are not [A],[A/],[/A/] or [/A]", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom


        My_C.Add X, Split(FindN_Replace_Array(X, 1), "/")  'Column A is split with "/" and each item is added to an array


        For Each element In Column_B


            On Error Resume Next


            T = Application.WorksheetFunction.Match(element, Column_B, 0)
            'location of the replacement row within column C if it exists
            String_Match = My_C.Item(X)


            For Y = 0 To UBound(String_Match)
                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(X, T))


            Next Y
            
            String_New = Join(String_Match, "/") 'joins the array back together again with a "/"


        Next element


        FindN_Replace_Array(X, 1) = String_New


        If Choice = True Then


            For Each Item In Column_B


                T = Application.WorksheetFunction.Match(Item, Column_B, 0)


                String_New = Replace(FindN_Replace_Array, Item, FindN_Replace_Array(T, 3))


            Next Item


        End If


    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
Click to expand...
Found another bug
Code: 
            For Y = 0 To UBound(String_Match)
                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(X, T))

[COLOR=#333333]            Next Y[/COLOR]
to
Code: 
[COLOR=#333333]            For Y = 0 To UBound(String_Match)[/COLOR]                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(T,3))

 [COLOR=#333333]            Next Y[/COLOR]
 
Upvote 0
MoshiM said:
Found another bug
Code: 
            For Y = 0 To UBound(String_Match)
                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(X, T))

[COLOR=#333333]            Next Y[/COLOR]
to
Code: 
[COLOR=#333333]            For Y = 0 To UBound(String_Match)[/COLOR]                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y),element, FindN_Replace_Array(T,3))

 [COLOR=#333333]            Next Y[/COLOR]
Click to expand...

And another Typo in the dim statement. Change New_String to String_New
 
Upvote 0
MoshiM said:
And another Typo in the dim statement. Change New_String to String_New
Click to expand...
Code: 
Sub Replace_Items()


    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual


Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
    Item As Variant, My_C As New Dictionary, New_String As String, String_Match() As Variant, Choice As Boolean


    FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).value2 'from the top row ro the last used row in columns A & C


    Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)


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


            FindN_Replace_Array(X, Y) = Replace(FindN_Replace_Array(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, Y) = Trim(FindN_Replace_Array(X, Y)) 'removes leading and trailing spaces from all items


        Next Y


    Next X


    If MsgBox("Do you want to replace all possible substring matches that are not [A],[A/],[/A/] or [/A]", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True


    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom


        My_C.Add X, Split(FindN_Replace_Array(X, 1), "/")  'Column A is split with "/" and each item is added to an array


        For Each element In Column_B


            On Error GoTo Skip


            T = Application.WorksheetFunction.Match(element, Column_B, 0)
            'location of the replacement row within column C if it exists
            String_Match = My_C.Item(X)


            For Y = 0 To UBound(String_Match)
                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y), FindN_Replace_Array(T, 3))


            Next Y
            
            New_String = Join(String_Match, "/") 'joins the array back together again with a "/"
            
Skip: err.Clear


        Next element


        FindN_Replace_Array(X, 1) = New_String


        If Choice = True Then
        
            For Each Item In Column_B
            
                On Error GoTo Skip_2
                
                T = Application.WorksheetFunction.Match(Item, Column_B, 0)


                New_String = Replace(FindN_Replace_Array, Item, FindN_Replace_Array(T, 3))
Skip_2: err.Clear
            
            Next Item


        End If


    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
I edited the code as your instructions, but it still doesn't like this portion in bold below. It's saying that it's a Type Mismatch. I

Code: 
Sub Replace_Items()



    Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual




Dim FindN_Replace_Array() As Variant, X As Long, element As Variant, Column_B() As Variant, T As Long, Y As Long, _
    Item As Variant, String_New As String, String_Match() As Variant, Choice As Boolean




    FindN_Replace_Array = ActiveSheet.Range("A1:C" & ActiveSheet.UsedRange.Rows.Count).Value2 'from the top row ro the last used row in columns A & C




    Column_B = Application.WorksheetFunction.Index(FindN_Replace_Array, 0, 2) 'column 2 of the Array




    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1)




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




            FindN_Replace_Array(X, Y) = Replace(FindN_Replace_Array(X, Y), Chr(34), vbNullString) 'removes quotation marks from all items
            FindN_Replace_Array(X, Y) = Trim(FindN_Replace_Array(X, Y)) 'removes leading and trailing spaces from all items




        Next Y




    Next X




    If MsgBox("Do you want to replace all possible substring matches that are not [A],[A/],[/A/] or [/A]", vbYesNo, "Replacement Type") _
            = vbYes Then Choice = True




    For X = LBound(FindN_Replace_Array, 1) To UBound(FindN_Replace_Array, 1) 'top to bottom




        My_C.Add X, Split(FindN_Replace_Array(X, 1), "/")  'Column A is split with "/" and each item is added to an array




        For Each element In Column_B




            On Error Resume Next




            T = Application.WorksheetFunction.Match(element, Column_B, 0)
            'location of the replacement row within column C if it exists
            String_Match = My_C.Item(X)




            For Y = 0 To UBound(String_Match)                'looks for exact matches for A[not part of substrings], A/ , /A/ , /A
                If String_Match(Y) = element Then String_Match(Y) = Replace(String_Match(Y), element, FindN_Replace_Array(T, 3))


             Next Y
            
            
            String_New = Join(String_Match, "/") 'joins the array back together again with a "/"




        Next element




        FindN_Replace_Array(X, 1) = String_New




        If Choice = True Then




            For Each Item In Column_B




                T = Application.WorksheetFunction.Match(Item, Column_B, 0)




                String_New = Replace([B]FindN_Replace_Array[/B], Item, FindN_Replace_Array(T, 3))




            Next Item




        End If




    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
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
980
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
994
rediffusion
rediffusion

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

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