I am trying to get VBA to replace values across various ranges in my excel data, this is mainly around changing numerical values.

Currently it will work with the following code to replace text strings with numerical values:

Sub distress_rating_scale_end_level_two()
  
  Range("CD:CD").Replace What:="0. No Distress", Replacement:="1", LookAt:=xlWhole
  Range("CD:CD").Replace What:="10. Extreme Distress", Replacement:="10", LookAt:=xlWhole
  Range("CD:CD").Replace What:="Not Recorded", Replacement:="11", LookAt:=xlWhole
  Range("CD:CD").Replace What:="DBI Not Started", Replacement:="12", LookAt:=xlWhole

End Sub

However in another column also need it to change numerical values to a different format i.e. “4” should become “04.”
I have tried the same logic as above:

Sub distress_rating_scale_end_level_two()
  
  Range("CC:CC").Replace What:="4", Replacement:="04.", LookAt:=xlWhole
  Range("CC:CC").Replace What:="1", Replacement:="01.", LookAt:=xlWhole

End Sub

This doesn’t seem to work and just keeps the format as a single numerical value.