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

 

[AlphaFrog]

I don't follow what the problem is. You give little info.

If you have anything except numeric values in the replacement list you could have errors. Perhaps one cell in the list has a trailing space in it that you don't see.

 

[bu5tarf]

Hi AlphaFrog, thank u so much for taking a look at this again, it is very much appreciated.

As you can see, i have specified the range to make up this specific replacement list.
If cell E1404, F1404 and G1404 contain headings in text, I think this is causing the prob because I deleted the headings (for the other replacement lists too) and re-run the macro and it nearly worked perfectly. (At least the run-time error isn't a problem no more )

        Set subList = .Range("E1405:E1415", .Range("E" & Rows.Count).End(xlUp))

The only small problem now, is one of the replacement values is 0 to replace the original values but for some reason, it replaces the original values with a blank cell instead of a 0... Do u have any idea's why this is happening???
Maybe I should try other feasible variable types (i.e. rList As .....)??

Many Thanks

 

[AlphaFrog]

If you want to specify the exact range for the list...

        Set subList = .Range([COLOR="Blue"]"E1405:E1415"[/COLOR][COLOR="Red"], .Range("E" & Rows.Count).End(xlUp)[/COLOR])

...you could remove the red part of the code. That part finds the last used cell in column E . Since you are specifying a specific range in column E ("E1405:E1415"), you don't need to find the last used cell in column E.

        Set subList = .Range("E1405:E1415")

...it replaces the original values with a blank cell instead of a 0

Perhaps you have the cells formatted to not display zeros. When you select a cell that should have a replacement zero in it, do you see a zero in the formula bar?

Or perhaps later in the replacement list, you are replacing zeros with something else or blanks.. If in the replacement list you have a row of cells that are all blanks, e.g. E1405 to G1405 is blank, that might replace zeros with blanks. I'm not sure. I haven't tested that concept.

 

[bu5tarf]

AlphaFrog - Once again THANK YOU SO SO SO MUCH!

You are a 1ST CLASS GENIUS!

Omitting this

, .Range("E" & Rows.Count).End(xlUp)

Did the trick!

 

[bu5tarf]

AlphaFrog,

Sorry to bother you again, one little problem and hopefully this will be the last!!

When the replacement list consists of decimal numbers, the macro doesn't seem to replace all of the numbers that fall into the range between the min and max ranges.

For example:

A B C
0 2.9 1.0000
3 5.9 0.8182
6 8.9 0.6364
9 11.9 0.4545
12 13.9 0.2727
14 15.9 0.0909
16 17.9 -0.0909
18 19.9 -0.2727
20 21.9 -0.4545
22 23.9 -0.6364
24 25.9 -0.8182
26 44 -1.0000

C is the replacement value (A and B are the min and max ranges respectively to search for before replace)

-0.0909
8.6
8.6
0.45454
8.9
0.45454
0.45454
8.8
0.45454
7.7

This is a small subset of the column (that should have been replaced with the replacement values) after running the macro. As you can see, 8.6, 8.9, 8.8, and 7.7 have not been replaced by their replacement value (0.6364). Its not just this range (6 to 8.9) some other values (like 19.9 that falls into a different range) aren't replacing properly neither. I have set the step (increment) to 0.1, and even tried it with 0.001... also tested it with changing the Variable Type to Double or Single... Single seems to work better.

Do u have any idea or suggestions as to why this might be happening?

Many thanks

 

[bu5tarf]

Sorry that replacement list isn't very clear... here goes attempt 2!

0 2.9 1.0000
3 5.9 0.8182
6 8.9 0.6364
9 11.9 0.4545
12 13.9 0.2727
14 15.9 0.0909
16 17.9 -0.0909
18 19.9 -0.2727
20 21.9 -0.4545
22 23.9 -0.6364
24 25.9 -0.8182
26 44 -1.0000

 

[bu5tarf]

Sorry i'm not sure how to insert an image from computer or a snapshot of the spreadsheet

 

[AlphaFrog]

This uses a different method to determine if a cell value falls within a numeric range. The original code was really intended to look for "Fords" as you requested in post #1 and not if a number falls within a range of two other numbers. This new code will replace any value if it falls within the two values from the list.

The code below is replacing the values from B1 to the last used cell in column B (in red). It doesn't matter if B1 is a header cell or not.

Sub Replace_List_Range_of_Vals()
    
    Dim rList As Range, cell As Range, c As Range
    
    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 c In [COLOR="Red"]Range("B1", Range("B" & Rows.Count).End(xlUp))[/COLOR]
        If IsNumeric(c) Then
            For Each cell In rList
                If c.Value >= cell.Value And c.Value <= cell.Offset(, 1).Value Then
                    c.Value = cell.Offset(, 2).Value
                    Exit For
                End If
            Next cell
        End If
    Next c
    
    ActiveSheet.AutoFilterMode = False
    Application.ScreenUpdating = True
    MsgBox "Replaced all items from the list.", vbInformation, "Replacements Complete"
    
End Sub

 

[bu5tarf]

Thank you very much AlphaFrog

 

[bu5tarf]

One more quick question....

If I wanted to keep the format (Number: 4 decimal places) of the replacement list and to insert the values into the replaced list with the same format, how would this be tackled?

It's because, a formula has been used to calculate the values in the replacement list, and even though the format is set a 4 decimal places, when the macro replaces the value in the replaced list, it replaces it without the format of 4 decimal places.

Many thanks