macro to replace old value for new ones based on a key

route217
route217 used Ask the Experts™
on
Hi Experts (see attached file.)

I have the following macro that does not work or gives me the wrong final value answer.

Here is my vba code:- see snippet

What i want the macro to do is replace any value in column T5: onwards with a new key value as shown in the attached file.

So if cell T5 has "1.3" as a value then replace it with "H" as per the key in attached worksheet
NOT give me "G.D" which the current macro is doing.

I hope this helps
Sub Replace_Values_()

Application.ScreenUpdating = False

    Dim rng As Range
    
    Set rng = Range([T5], Cells(Rows.Count, "W").End(xlUp).Offset(0, -3))
      rng.Replace "1", "G"
      rng.Replace "1.3", "H"
      rng.Replace "1.4", "G"
      rng.Replace "2", "E"
      rng.Replace "2.1", "F"
      rng.Replace "2.2", "F"
      rng.Replace "3", "D"
      rng.Replace "3.1", "E"
      rng.Replace "3.2", "D"
      rng.Replace "3.3", "C"
      rng.Replace "4", "C"
      rng.Replace "4.1", "C"
      rng.Replace "4.2", "B"
      rng.Replace "5", "B"
      rng.Replace "5.1", "B"
      rng.Replace "5.2", "A"
      rng.Replace "6", "A"
      rng.Replace "", "UNKNOWN"
      rng.Replace "A", "A"
      rng.Replace "B", "B"
      rng.Replace "C", "C"
      rng.Replace "C (Equiv)", "C"
      rng.Replace "D", "D"
      rng.Replace "E", "E"
      rng.Replace "F", "F"
      rng.Replace "G", "G"
      rng.Replace "Level 1", "G"
      rng.Replace "Level 2", "E"
      rng.Replace "Level 3", "D"
      rng.Replace "Level 4", "C"
      rng.Replace "Level 5", "B"
      rng.Replace "Level 6", "A"
      rng.Replace "Level 7", "1"
      rng.Replace "MT", "UNKNOWN"
      rng.Replace "N/A", "UNKNOWN"
      rng.Replace "na", "UNKNOWN"
      rng.Replace "tbc", "UNKNOWN"
            
Application.ScreenUpdating = True

End Sub

Open in new window

Key-.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Quality Lead Engineer
Top Expert 2011
Commented:
Basically it's doing a partial replacement ... try:

Chris
Option Explicit

Sub Replace_Values_()
 
Application.ScreenUpdating = False
 
    Dim rng As Range
     
    Set rng = Range([T5], Cells(Rows.Count, "W").End(xlUp).Offset(0, -3))
      rng.Replace "1", "G", xlWhole
      rng.Replace "1.3", "H", xlWhole
      rng.Replace "1.4", "G", xlWhole
      rng.Replace "2", "E", xlWhole
      rng.Replace "2.1", "F", xlWhole
      rng.Replace "2.2", "F", xlWhole
      rng.Replace "3", "D", xlWhole
      rng.Replace "3.1", "E", xlWhole
      rng.Replace "3.2", "D", xlWhole
      rng.Replace "3.3", "C", xlWhole
      rng.Replace "4", "C", xlWhole
      rng.Replace "4.1", "C", xlWhole
      rng.Replace "4.2", "B", xlWhole
      rng.Replace "5", "B", xlWhole
      rng.Replace "5.1", "B", xlWhole
      rng.Replace "5.2", "A", xlWhole
      rng.Replace "6", "A", xlWhole
      rng.Replace "", "UNKNOWN", xlWhole
      rng.Replace "A", "A", xlWhole
      rng.Replace "B", "B", xlWhole
      rng.Replace "C", "C", xlWhole
      rng.Replace "C (Equiv)", "C", xlWhole
      rng.Replace "D", "D", xlWhole
      rng.Replace "E", "E", xlWhole
      rng.Replace "F", "F", xlWhole
      rng.Replace "G", "G", xlWhole
      rng.Replace "Level 1", "G", xlWhole
      rng.Replace "Level 2", "E", xlWhole
      rng.Replace "Level 3", "D", xlWhole
      rng.Replace "Level 4", "C", xlWhole
      rng.Replace "Level 5", "B", xlWhole
      rng.Replace "Level 6", "A", xlWhole
      rng.Replace "Level 7", "1", xlWhole
      rng.Replace "MT", "UNKNOWN", xlWhole
      rng.Replace "N/A", "UNKNOWN", xlWhole
      rng.Replace "na", "UNKNOWN", xlWhole
      rng.Replace "tbc", "UNKNOWN", xlWhole
             
Application.ScreenUpdating = True
 
End Sub

Open in new window

route217Junior

Author

Commented:
thanks chris

i see my mistake.....
route217Junior

Author

Commented:
Hi chris,

Apologies for asking but can you shed some light on this problem

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25134096.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial