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?
Thank you,
Jared Z.
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?
Code:
[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.
