Macro to Find and Replace Cell References

[TooZippy]

I used this macro and it works to a certain extent. At first, I was having it look for "$M", "$N", "$O", “$P”, “$Q”, “$R”, “$S”,“$T”, “$U”. These cell references are the result after deleting a few columns in another spreadsheet. They are t be replaced with "$Q", "$R", "$S", “$T”, “$U”, “$V”, “$W”,“$X”, “$Y” (in that order) in a range of formulas off to the right.

It did not work since I had "$Q", "$R", "$S", “$T”, “$U” in both the Find and Replace. I took that out and it worked except that the order of the cell references in the formulas was mixed up. I need to have $Y cell reference at the far right of the formulas because I have a sum formula adding up the columns to the left of this column. Can anybody help please?

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Sub Find_and_Replace()[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000] [/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Dim Rng As Range[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Dim InputRng As Range, ReplaceRng As Range[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]xTitleId = "KutoolsforExcel"[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Set InputRng = Application.Selection[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Set InputRng = Application.InputBox("Formulas to Fix ",xTitleId, InputRng.Address, Type:=8)[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Set ReplaceRng = Application.InputBox("Find and Replace Values:", xTitleId, Type:=8)[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Application.ScreenUpdating = False[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]For Each Rng In ReplaceRng.Columns(1).Cells[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]    [/COLOR][COLOR=#000000]InputRng.Replacewhat:=Rng.Value, replacement:=Rng.Offset(0, 1).Value[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Next[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]Application.ScreenUpdating = True[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][COLOR=#000000]End Sub[/COLOR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]

Thank you,

Jared Z.

 

[TooZippy]

Here is a table of what I am trying to find and replace along with the results with the above formula. Can anybody help please?

Thank you,

Jared Z.

Find	Replace	Result
$I	$Q	$U	Col. 1
$J	$R	$V	Col. 2
$k	$S	$W	Col. 3
$L	$T	$X	Col. 4
$M	$U	$Y	Col. 5
$N	$V	$R	Col. 6
$O	$W	$S	Col. 7
$P	$X	$T	Col. 8
$Q	$Y	$U	Col. 9

<colgroup><col width="64" style="width:48pt" span="4"> </colgroup><tbody>
</tbody>