Multiple Find and Replace Using a List of Old Values with New Values

[JerryMason]

Hey guys.

I'm new with VBA and need to replace different values in my dataset with specific new values.
For instance, in Sheet1 I have values like $5.00M and need to turn that into $5,000,000. So, I dedcued I could search for ".00M" and replace it with Six Zeroes "000000", the result will be 5,000,000.

But as you can imagine, I have 100 variations of that from .00M to .99M and want to create a Macro that just handles a predefined list of the values in one shot.

I found this macro template which is the closest but I'm confused as to how it handles it exactly. When you press Start, it asks you to define the "OLD" value range, then you select the "NEW" value range.

Sub Replace_MultiValues()
Dim R As Range
Dim InputR As Range, ReplaceR As Range
xTitleId = "Choose Range"
Set InputR = Application.Selection
Set InputR = Application.InputBox("Old ", xTitleId, InputR.Address, Type:=8)
Set ReplaceR = Application.InputBox("New :", xTitleId, Type:=8)
Application.ScreenUpdating = False
For Each R In ReplaceR.Columns(1).Cells
    InputR.Replace What:=R.Value, Replacement:=R.Offset(0, 1).Value
Next
Application.ScreenUpdating = True
End Sub

Except that didn't work for me.
So, can someone tell me how I can customize the above code to
1. Take the OLD values in SHEET2 column A
2. Replace the OLD values with the "NEW" value (which is in the adjacent cell in Sheet2:Column B) in:
3. The dataset in the entirety of Sheet1

Thanks in advance.

 

[JGordon11]

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	17.55M	24.8M	52.22M	24.62M	66.36M	84.4M		17550000	24800000	52220000	24620000	66360000	84400000
2	22.03M	57.47M	9.69M	45.37M	14.89M	93.79M		22030000	57470000	9690000	45370000	14890000	93790000
3	90.52M	7.25M	56.53M	66.83M	29.86M	25.38M		90520000	7250000	56530000	66830000	29860000	25380000
4	16.71M	32.43M	68.68M	61.17M	85.39M	9.04M		16710000	32430000	68680000	61170000	85390000	9040000
5	5.87M	46.66M	66.78M	17.4M	5.49M	86.61M		5870000	46660000	66780000	17400000	5490000	86610000
6	82.41M	63.24M	48.17M	92.64M	55.51M	98.16M		82410000	63240000	48170000	92640000	55510000	98160000
7	85.91M	89.84M	50.97M	72.78M	61.19M	9.87M		85910000	89840000	50970000	72780000	61190000	9870000
8	8.28M	41.11M	37.18M	27.32M	52.38M	65.85M		8280000	41110000	37180000	27320000	52380000	65850000
9	46.06M	89.69M	89.52M	58.75M	56.13M	30.23M		46060000	89690000	89520000	58750000	56130000	30230000
10	39.8M	45.55M	17.88M	46.97M	91.18M	47M		39800000	45550000	17880000	46970000	91180000	47000000
11	74.93M	84.81M	93.83M	44.62M	53.13M	55.28M		74930000	84810000	93830000	44620000	53130000	55280000
12	41.98M	32.35M	97.63M	30.54M	87.04M	86.8M		41980000	32350000	97630000	30540000	87040000	86800000
13	73.83M	29.25M	3.62M	57.2M	12.13M	24.06M		73830000	29250000	3620000	57200000	12130000	24060000
14	10.81M	89.29M	90.33M	99.34M	6.66M	57.64M		10810000	89290000	90330000	99340000	6660000	57640000
15	37.53M	63.28M	30.06M	82.31M	31.1M	26.27M		37530000	63280000	30060000	82310000	31100000	26270000
16	9.13M	46.87M	35M	78.14M	14.89M	78.75M		9130000	46870000	35000000	78140000	14890000	78750000
17	47.71M	63.53M	6.06M	71.3M	55.54M	70.59M		47710000	63530000	6060000	71300000	55540000	70590000
18	26.8M	22.32M	75.71M	53.11M	5.68M	91.92M		26800000	22320000	75710000	53110000	5680000	91920000
19	14.82M	81.37M	35.48M	62.53M	92.73M	95.66M		14820000	81370000	35480000	62530000	92730000	95660000
20	84.24M	30.42M	94.42M	77.21M	9.66M	36.17M		84240000	30420000	94420000	77210000	9660000	36170000
21
Sheet4

Cell Formulas
Range	Formula
H1:M20	H1	=LEFT(A1:F20, LEN(A1:F20)-1)*1000000
Dynamic array formulas.

 

[Peter_SSs]

If you want to stick with a vba approach, what about doing them all at once?

Sub Replace_MultiValues_v2()
  Dim R As Range
  Dim InputR As Range, ReplaceR As Range
  Const xTitleId As String = "Choose Range"
  
  Set InputR = Application.Selection
  Set InputR = Application.InputBox("Old ", xTitleId, InputR.Address, Type:=8)
  Set ReplaceR = Application.InputBox("New (Top-left cell):", xTitleId, Type:=8)
  With InputR
    ReplaceR.Resize(.Rows.Count, .Columns.Count).Value = Evaluate("substitute(" & .Address & ",""M"","""")*10^6")
  End With
End Sub