Compare and replace

[kitwit]

Hi,

I am trying to use this replace text/phrase by comparing the values into diff sheet, if the the comparison exist, then replace a different text,

it works when i use it for a few words value, but i have to replace a long phrases, i get mismatch error. Please help. I am using this to compare and replace phrases, not just one or two values.

Sub MultiFindNReplace()'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, _
                 replacement:=Rng.Offset(0, 1).Value, _
                 LookAt:=xlWhole


                 
Next
Application.ScreenUpdating = True
End Sub

 

[kitwit]

i get type mismatch error 13. It works well when replacing with short text or value, but when replacing phrases it doesn't seems to work

Hi,

I am trying to use this replace text/phrase by comparing the values into diff sheet, if the the comparison exist, then replace a different text,

it works when i use it for a few words value, but i have to replace a long phrases, i get mismatch error. Please help. I am using this to compare and replace phrases, not just one or two values.

Sub MultiFindNReplace()'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Original Range ", xTitleId, InputRng.Address, Type:=8)
Set ReplaceRng = Application.InputBox("Replace Range :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each Rng In ReplaceRng.Columns(1).Cells
InputRng.Replace what:=Rng.Value, _
                 replacement:=Rng.Offset(0, 1).Value, _
                 LookAt:=xlWhole


                 
Next
Application.ScreenUpdating = True
End Sub