VBA Replace cell value with cell contains

[tryingharder]

Hi,

I'm looking to use the replace to change a known value to another value.

but i have multiple values to i want to list them all out in a sheet and loop

once i've replaced the values in the list i want to loop and do the same in another work book

for example my list is

Change from	Change to
3037364	3055059
3055059	3055019
3055019	3081093
3081093	3055065
3055065	3081094
3081094	3055020
3055020	3037364

but my code isn't working

Sub replace()
'
' replace Macro
'

'
Windows(Range("S14").Value).Activate 'CHANGE THIS WHEN YOU WANT TO USE THE MLPL NOT THE TEST SHEET
Range("D6:D7").Select
Range("D6").Activate
Selection.replace What:=(Range("N14").Value), Replacement:=(Range("O14").Value), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
Windows("macro copy replace.xlsm").Activate
End Sub

 

[Joe4]

Welcome to the Board!

Let's focus on the one workbook first.

What sheet is the list of replacements found on (sheet name, please)?
And what range on the sheet are this replacements listed in (exact range address, please)?

And then what sheet contains the data that you want to do the replacements in (sheet name, please)?
And what range exactly should it be looking in to do the replacements (range address please)?

 

[tryingharder]

Hi, thanks for the reply
I'm running the macro on workbook "macro copy replace" which holds the records to be changed and to what and where, it lists the value to find and lists the value to change it to, - "sheet1"


address of workbook of records to be changed - I want to list multiple workbooks and do the replace in each workbook one after the other in cells t14 to t150 - but if you need the name of the first one "Zone 1 MLPL"
on sheet - again I want to list multiple tabs and do the replace in each sheet one after the other in cells s14 to s150 - but if you need the name of the first one "A40101"


the values that need to be change are only in the whole of column G and R

 

[Joe4]

Hmmm, your answer is a bit confusing.

As I said, let's focus on ONE workbook here first, and try to get that working, before we worry about multiple workbooks.

I'm running the macro on workbook "macro copy replace" which holds the records to be changed and to what and where, it lists the value to find and lists the value to change it to, - "sheet1"

So, if the workbook with the value to replace totally separate from the workbooks where you want to do the replacment?
On the workbook that contains the value to replace, you mentiond that they appear on "Sheet1", but did not answer my question regarding what the address of the range where they exist is.

I want to list multiple workbooks and do the replace in each workbook one after the other in cells t14 to t150

again I want to list multiple tabs and do the replace in each sheet one after the other in cells s14 to s150

So I am confused. Are the values we want to replace actually in the range T14:T150, or S14:S150?
Or does it vary by sheet?
If it does vary by sheet, how can we determine which range to look in?

It may be helpful if you post samples of your data from your two different files, so we can see how it is all laid out, and where everything exists.

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.

 

[tryingharder]

Hi

yes i am probably using the wrong words. but the work book "Zone 1 MLPL" sheet "A40101" has data in it, somewhere in both column G and R that needs to be changed and the workbook "macro copy replace" sheet "sheet1" has a list of values in "N14" that needs to be found in "Zone 1 MLPL" sheet "A40101" and replaced with values from "O14" in "macro copy replace" sheet "sheet1"


it's not my computer so i can't install anything. sorry. i can include files if that helps.

 

[Joe4]

OK, if you cannot post images, you are going to have to be much more detailed in your descriptions (or at least post screen images where I can see the row and column headers).

You say that the values you want to replace are in "cell N14", but that is only one cell, and the values you shown wanting to replace is a 2x7 grid (14 cells). So it has to be a lot more cells that just "N14".

I would also like to see a sample of the data on the "A40101" sheet.

 

[tryingharder]

Hi,

hopfully the images make sense of what i've said

thanks

 

[Joe4]

Thanks for that.

Place this VBA code in your "macro copy replace" workbook and save.
Then, with both this workbook and your "Zone 1 MLPL.xlsx" workbook open, try running the code, and it should update columns G and R.

Sub MyReplaceMacro()

    Dim mcrWB As Workbook
    Dim datWB As Workbook
    Dim mcrWS As Worksheet
    Dim datWS As Worksheet
    Dim lr As Long
    Dim r As Long
    Dim fn As String
    Dim rp As String
    
    Application.ScreenUpdating = False
    
'   Set macro workbook variable to this workbook
    Set mcrWB = ThisWorkbook
'   Set worksheet where values to replace exist
    Set mcrWS = mcrWB.Sheets("Sheet1")
    
'   Activate data workbook (assuming already opened)
    Windows("Zone 1 MLPL.xlsx").Activate
'   Set data workbook variable to the data workbook
    Set datWB = ActiveWorkbook
'   Set worksheet where values to replace exist
    Set datWS = datWB.Sheets("A40101")
    
'   Find last row with data in column N on macro replace sheet
    mcrWB.Activate
    lr = mcrWS.Cells(Rows.Count, "N").End(xlUp).Row
    
'   Loop through all values on macro sheet, starting with row 14
    For r = 14 To lr
'       Capture find and replace values
        fn = mcrWS.Cells(r, "N").Value
        rp = mcrWS.Cells(r, "O").Value
'       Go to data workbook/worksheet and do replacements
        datWB.Activate
        datWS.Activate
'       Do replacements on columns G and R
        On Error Resume Next
        Columns("G:G").Replace What:=fn, Replacement:=rp, LookAt:=xlWhole _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        Columns("R:R").Replace What:=fn, Replacement:=rp, LookAt:=xlWhole _
            , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
        On Error GoTo 0
    Next r

    Application.ScreenUpdating = True

    MsgBox "Macro complete!"
    
End Sub