Macros/VBA Code for repetitive 'find and replace'

[bu5tarf]

Hi,

Does anyone know the VBA code for a macro in excel to record the motion of finding and then substituting values from individual cells from one column for the individual cells in another column?

For example:

Cells A2 to A48 in workbook1 contains a list of many different values (Peugeot, Alfa Romeo, Ford, Renault etc...) and in cells B2 to B48 are their corresponding decimal values that need to replace them in another workbook (workbook2).

In Column A of workbook2 has thousands of instances of the values present in A2 to A48 of workbook1. The numeric values from B2 to B48 (lets say ford = 0.1 and peugeot = -0.4) need to replace the original text data in column A of workbook 2. (so 0.1 replaces ford and -0.4 replaces peugeot)

I've tried using macros but it will only run the exact steps it recorded and not move down the cells even when using relative references.

Because there are 2000 rows of data, using copy (from A2) and paste (into find from find and replace), and then copy( from B2) and paste into replace (from find and replace) is proving very tedious and time consuming as there are 24 columns to do this for (not forgetting the 2000 rows!).

Please Help!!!

Thanks

 

[bu5tarf]

The Replace_List macro code goes in the workbook that has the replacement list.

The replacement list is assumed to be on the 1st sheet in that workbook in columns A and B

    With ThisWorkbook.Sheets(1)
        Set rList = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With

The code isn't replacing with random decimal numbers.

Try changing...

                                  LookAt:=xlPart, _

To

                                  LookAt:=xlWhole, _

This will look for an exact match instead of a partial match. I didn't realize you were searching for numbers.

AlphaFrog you are a genius!

What if I wanted to run this macro to apply to one column at a time instead of it replacing the values present in the entire sheet at one time?

For example, run the macro to replace all values present in column A.
Then amend the macro or create a new macro with more or less the same code to replace the values in column B and so on...?

Thank you so much!!!

 

[AlphaFrog]

This will replace just in column A

ActiveSheet.[COLOR="Red"]Columns("A")[/COLOR].Replace What:=cell.Value, _

This will replace in columns B to G

ActiveSheet.[COLOR="Red"]Columns("B:G")[/COLOR].Replace What:=cell.Value, _

This will replace in whatever range you have selected (columns, rows, or a block of cells) when you run the macro.

ActiveSheet.[COLOR="Red"]Selection[/COLOR].Replace What:=cell.Value, _

You're welcome. I'm glad it worked for you.

 

[bu5tarf]

So far, its brilliant!

Can you suggest a way around this:

Some replacement values, cover a range of values from the original spreadsheet for example

< 400 = 1 (1 being the replacement value)
401 - 500 = 0.8
501 - 600 = 0.6
So for example there will be 501, 501, 508, 550, 551, 552 etc... present in the original spreadsheet and all of their corresponding replacement values will be 0.6.

I was thinking of creating a column for each possible value and in the adjacent column put in their corresponding replacement value. Instead of doing this, would it be possible to tell the macro to replace all values in that range with the value in the adjacent column (using Replacement:=cell.Offset(0, 1).Value, _ )

Set rList = .Range("A30:A35", .Range("A" & Rows.Count).End(xlUp))

So say A30 contains 501 - 600 and B30 contains 0.6

Is that possible instead of listing all values between 501 -600 individually?

Thanks Again AlphaFrog

 

[AlphaFrog]

In the replacement list, column A is the start value, column B is the end value, Column C is the replacement value.

Example replacement list...

Excel Workbook
	A	B	C
1	5	10	0.1
2	12	12	0.2
3	15	17	0.3
4	55	56	0.99
Sheet

The code below is using the previously discussed Selection method to replace value within...

Sub Replace_List()
    
    Dim rList As Range, cell As Range, n As Long
    
    If ActiveWorkbook.Name = ThisWorkbook.Name Then
        MsgBox "1st select the workbook and worksheet with the data you want to replace." & vbLf & _
        "Then run this macro again.", vbExclamation, "Select the Data Worksheet"
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
        
    With ThisWorkbook.Sheets(1)
        Set rList = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
    
    For Each cell In rList
        For n = cell.Value To cell.Offset(0, 1).Value Step 1
            Selection.Replace What:=n, _
                              Replacement:=cell.Offset(0, 2).Value, _
                              LookAt:=xlWhole
        Next n
    Next cell
    
    Application.ScreenUpdating = True
    
    MsgBox "Replaced all items from the list.", vbInformation, "Replacements Complete"
    
End Sub

 

[bu5tarf]

Many thanks AlphaFrog and sorry to bother you again.

 For Each cell In rList
        For n = cell.Value To cell.Offset(0, 1).Value Step 1
            Selection.Replace What:=n, _
                              Replacement:=cell.Offset(0, 2).Value, _
                              LookAt:=xlWhole
        Next n
    Next cell

How does the macro know to only replace items in a specific column (for example column D) because previously this was used

ActiveSheet.Columns("B").Replace What:=cell.Value, _

I tried to add 'ActiveSheet.Columns("B")' before

Selection.Replace What:=n, _

But kept on getting an error.
If this is omitted, then this code does work but replaces only one cell each time the script is run bearing in mind I have added this

 With ThisWorkbook.Sheets(1)
        Set rList = .Range("E1405:E1415", .Range("E" & Rows.Count).End(xlUp))

Thank you for your time AlphaFrog... very much appreciate it!

 

[AlphaFrog]

This should work to replace only in column B

ActiveSheet.Columns("B").Replace What:=n, _

 

[bu5tarf]

It works for searching finding and replacing a range of integers but not decimal numbers.

Keep getting a 'Type mismatch error '13' ' error message. when clicking Debug it points to

 For n = cell.Value To cell.Offset(0, 1).Value Step 1

Have tried to change Var type

n As Decimal

but this throws up even more error messages

Any ideas what could be causing this error?

 

[AlphaFrog]

n As [COLOR="Red"]Single[/COLOR]

Excel VBA Variable types


Also, in this line of code...

For n = cell.Value To cell.Offset(0, 1).Value Step [COLOR="Red"]1[/COLOR]

...the red 1 is the increment value. So if in the replacement list, A was 400.1 and B was 410.1, then it will increment 400.1, 401.1, 402.1, 403.3, ...410.1

You can change the red 1 to a decimal value if you want.

 

[bu5tarf]

Ok now for some reason, it's throwing up a run time error 13 - type mismatch even when those details you advised are attended to... its baffling me because the first time this macro was run, it seemed to work with no problems.

If this is any clue as to whats going on, the macro seems to be able to replace all values except those who's replacement value is 0 and those cells have no value in them anymore (not even their original value)

Do u have any idea why it might be doing this?

 

[bu5tarf]

Still pointing to the same row of code when debug is clicked.

 For n = cell.Value To cell.Offset(0, 1).Value Step 1