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]

Add the line in red...

                If c.Value >= cell.Value And c.Value <= cell.Offset(, 1).Value Then
                    c.Value = cell.Offset(, 2).Value
                    [COLOR="Red"]c.Value.NumberFormat = "0.0000"[/COLOR]
                    Exit For
                End If

 

[bu5tarf]

Cheers!

 

[AlphaFrog]

Correction (for what it's worth).

This...
c.Value.NumberFormat = "0.0000"

Should have been just this...
c.NumberFormat = "0.0000"