change input range in VBA code

[perahlgren]

Hi,

I want to set the input range and the replace range to fixed. Is that possible?

//Per

Sub MultiFindNReplace()
'Update 20140722
Dim Rng As Range
Dim InputRng As Range, ReplaceRng As Range
xTitleId = "ersätt"
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
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Hi & welcome to the board.

yes it's possible, what ranges do you want to use?

 

[NdNoviceHlp]

Hi perahlgren and Welcome to the Board! Sure it's possible. Care to share what the fixed input and replace ranges are? Dave

 

[perahlgren]

Hi Thanks alot,
The translation table is in sheet1 range J2:K60 and the original data is in sheet2 column F.
//Per

 

[Fluff]

Try

Sub MultiFindNReplace()
'Update 20140722
   Dim Rng As Range
   Dim InputRng As Range, ReplaceRng As Range
   
   Set InputRng = Sheets("Sheet1").Range("J2:K60")
   Set ReplaceRng = Sheets("Sheet2").Range("F1", Sheets("Sheet2").Range("F" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
   For Each Rng In ReplaceRng
      InputRng.Replace what:=Rng.Value, replacement:=Rng.Offset(0, 1).Value
   Next
Application.ScreenUpdating = True
End Sub

 

[perahlgren]

It works like a sharm, thanks.

 

[Fluff]

Glad to help & thanks for the feedback