Find and Replacing text in formula via VBA

[MishGerber]

Hi All,

I am trying to replace an equipment number in a formula with the contents in a specific cell per row. I have the VBA text string for the replacing of one row, but I need assistance in duplicating the action for each row where each replacement text is in a different cell. below is the current macro.

Sub FINDANDREPLACE()
'
' FINDANDREPLACE Macro
' FIND AND REPLACE EQUIP. NO IN FORMULA WITH VALUE IN COLUMN C
'
' Keyboard Shortcut: Ctrl+Shift+H
'
Range("D157").Select
ActiveCell.FormulaR1C1 = _
"=SUM(SUMIFS('R&M RAW22-23'!R2C4:R15000C4, 'R&M RAW22-23'!R2C1:R15000C1,""JULY"",'R&M RAW22-23'!R2C8:R15000C8,{""700109955""}))"
Range("C157").Select
Selection.Copy
Range("C157:O157").Select
Selection.Replace What:="700109955", Replacement:="700240778", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

I want to keep Replace What:="700109955" the same the entire time, the only thing that needs to change is the Replacement value.

Any help will be appreciated.

 

[Joe4]

Welcome to the Board!

A few more details are needed:
- what range of cells contains the values that you are replacing "700109955" with?
- what is the full range of cells you are looking to do replacements in?
- will each value only be found once, or could they appear multiple times?
- are you actually populating the formulas also, or just replacing values in existing formula?

Posting a sample of your data and expected results may also be helpful.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MishGerber]

Welcome to the Board!

A few more details are needed:
- what range of cells contains the values that you are replacing "700109955" with?
- what is the full range of cells you are looking to do replacements in?
- will each value only be found once, or could they appear multiple times?
- are you actually populating the formulas also, or just replacing values in existing formula?

Posting a sample of your data and expected results may also be helpful.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Hi there,

Let me answer each point in order:
- the Range of cells that contain the replacement values are C8:C612
- the full range of cells is D7:O612 (all these cells have the formula in)
- each value only appears once in the formula
- the formulas are already populated, just the Equipment number needs to change

Each row is a different equipment number but each formula reflects 700109955. it needs to change per row.

At the moment, I am currently copying the value in the C cell, then highlighting the cells from D to O in the row, then using the find and select option to find 700109955 and replace it with the value of the C cell. I am hoping to automate this process with a Macro if possible as the above method is time consuming as I have 612 rows of data to change.

There are 12 columns, one for each month of the year, so each column's formula is different in the red highlighted section to reflect the correct month (E.g. Column D=July, Column E=August etc.)

Below is a sample of my worksheet. The first row is the result I am looking for, and the rows below need to have the value changed.

700243054	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JULY",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"AUGUST",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"SEPTEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"OCTOBER",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"NOVEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"DECEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JANUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"FEBRUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MARCH",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"APRIL",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MAY",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JUNE",'R&M RAW22-23'!$H$2:$H$15000,{"700243054"}))
700243057	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JULY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"AUGUST",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"SEPTEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"OCTOBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"NOVEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"DECEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JANUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"FEBRUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MARCH",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"APRIL",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MAY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JUNE",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))
700243065	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JULY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"AUGUST",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"SEPTEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"OCTOBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"NOVEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"DECEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JANUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"FEBRUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MARCH",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"APRIL",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MAY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JUNE",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))
700243066	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JULY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"AUGUST",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"SEPTEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"OCTOBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"NOVEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"DECEMBER",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JANUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"FEBRUARY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MARCH",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"APRIL",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"MAY",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))	=SUM(SUMIFS('R&M RAW22-23'!$D$2:$D$15000, 'R&M RAW22-23'!$A$2:$A$15000,"JUNE",'R&M RAW22-23'!$H$2:$H$15000,{"700109955"}))

 

[Joe4]

Why not just have your formulas dynamically reference the values in column C instead of hard-coding them?
I.e, instead of having your formula reference a hard-coded value like "700243054", why not just have it reference the value from column C of that same row, i.e. C8?
Then basically you have the same formula down the whole column, and do not need any VBA code at all.

 

[MishGerber]

Why not just have your formulas dynamically reference the values in column C instead of hard-coding them?
I.e, instead of having your formula reference a hard-coded value like "700243054", why not just have it reference the value from column C of that same row, i.e. C8?
Then basically you have the same formula down the whole column, and do not need any VBA code at all.

Thank you kindly. I tried it and it works. Saved me hours of changing it manually.

 

[Joe4]

You are welcome.
Glad I was able to help!