VBA: Shortening Find & Replace Macro

[MASOODAHMAD]

Hi,

Is there a shorter way to achieve the same result by shortening the VBA code. Please explain in detail if it can be done, so that I can edit it in future if there are some changes.

Here is the Macro:

Sub FindReplaceFor_RD()

Cells.Replace What:="A1", Replacement:="A01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A2", Replacement:="A02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A3", Replacement:="A03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A4", Replacement:="A04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A5", Replacement:="A05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A6", Replacement:="A06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A7", Replacement:="A07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A8", Replacement:="A08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="A9", Replacement:="A09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="B1", Replacement:="B01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B2", Replacement:="B02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B3", Replacement:="B03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B4", Replacement:="B04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B5", Replacement:="B05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B6", Replacement:="B06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B7", Replacement:="B07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B8", Replacement:="B08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="B9", Replacement:="B09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="C1", Replacement:="C01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C2", Replacement:="C02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C3", Replacement:="C03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C4", Replacement:="C04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C5", Replacement:="C05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C6", Replacement:="C06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C7", Replacement:="C07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C8", Replacement:="C08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="C9", Replacement:="C09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="D1", Replacement:="D01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D2", Replacement:="D02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D3", Replacement:="D03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D4", Replacement:="D04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D5", Replacement:="D05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D6", Replacement:="D06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D7", Replacement:="D07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D8", Replacement:="D08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="D9", Replacement:="D09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="E1", Replacement:="E01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E2", Replacement:="E02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E3", Replacement:="E03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E4", Replacement:="E04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E5", Replacement:="E05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E6", Replacement:="E06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E7", Replacement:="E07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E8", Replacement:="E08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="E9", Replacement:="E09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="F1", Replacement:="F01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F2", Replacement:="F02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F3", Replacement:="F03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F4", Replacement:="F04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F5", Replacement:="F05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F6", Replacement:="F06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F7", Replacement:="F07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F8", Replacement:="F08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="F9", Replacement:="F09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="G1", Replacement:="G01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G2", Replacement:="G02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G3", Replacement:="G03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G4", Replacement:="G04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G5", Replacement:="G05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G6", Replacement:="G06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G7", Replacement:="G07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G8", Replacement:="G08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="G9", Replacement:="G09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="H1", Replacement:="H01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H2", Replacement:="H02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H3", Replacement:="H03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H4", Replacement:="H04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H5", Replacement:="H05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H6", Replacement:="H06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H7", Replacement:="H07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H8", Replacement:="H08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="H9", Replacement:="H09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="I1", Replacement:="I01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I2", Replacement:="I02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I3", Replacement:="I03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I4", Replacement:="I04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I5", Replacement:="I05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I6", Replacement:="I06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I7", Replacement:="I07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I8", Replacement:="I08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="I9", Replacement:="I09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="J1", Replacement:="J01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J2", Replacement:="J02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J3", Replacement:="J03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J4", Replacement:="J04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J5", Replacement:="J05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J6", Replacement:="J06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J7", Replacement:="J07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J8", Replacement:="J08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="J9", Replacement:="J09", _
LookAt:=xlWhole, MatchCase:=False


Cells.Replace What:="K1", Replacement:="K01", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K2", Replacement:="K02", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K3", Replacement:="K03", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K4", Replacement:="K04", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K5", Replacement:="K05", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K6", Replacement:="K06", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K7", Replacement:="K07", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K8", Replacement:="K08", _
LookAt:=xlWhole, MatchCase:=False

Cells.Replace What:="K9", Replacement:="K09", _
LookAt:=xlWhole, MatchCase:=False


End Sub





Thanks in advance.

Masood

 

[Andrew Poulsom]

Here's some code that loops around the letter and numbers:

Sub FindReplaceFor_RD()
    Dim i As Long, j As Long
    For i = 65 To 75
        For j = 1 To 9
            Cells.Replace What:=Chr(i) & j, Replacement:=Chr(i) & Format(j, "00"), _
            LookAt:=xlWhole, MatchCase:=False
        Next j
    Next i
End Sub

The letter A to K are ASCII characters 65 to 75.

 

[MASOODAHMAD]

Hi Andrew,

Thanks for the code, It's really short. But what if I would like to search in a selected column or columns.

Please advise.

Thanks,
Masood

 

[Andrew Poulsom]

Use the Columns property instead of the Cells property.

 

[MASOODAHMAD]

Hi,

Here is the modified version:

Sub RD()
Dim i As Long, j As Long
For i = 65 To 75
For j = 1 To 9
Columns.Replace What:=Chr(i) & j, Replacement:=Chr(i) & Format(j, "00"), _
LookAt:=xlWhole, MatchCase:=False
Next j
Next i
End Sub

But, it is not working. The Macro is replacing on the whole sheet.

Please advise,

Thanks,
Masood

 

[Andrew Poulsom]

For column A:

Columns("A").Replace What:=Chr(i) & j, Replacement:=Chr(i) & Format(j, "00"), _
    LookAt:=xlWhole, MatchCase:=False

 

[MASOODAHMAD]

Hi Andrew,

Thanks for a wonderful session.

I also tried with the below code.

Sub FindReplaceFor_RD()
Dim i As Long, j As Long
For i = 65 To 75
For j = 1 To 9
Selection.Columns.Replace What:=Chr(i) & j, Replacement:=Chr(i) & Format(j, "00"), _
LookAt:=xlWhole, MatchCase:=False
Next j
Next i
End Sub


Thanks for all your help.

Masood Ahmad