Excel Find and replace using variables

Question

I'd like to know if there's a way to FIND AND REPLACE in this way :

I need to find all cells that contains apt followed by a number. But the number is always different. So is there a script or something that would do that?

FIND : apt XX (the 2 Xs representing the numbers)

REPLACE BY : app 30

So it would locate all the places where apt + a number

Because I have thousands of lines, and I don't want to do :

FIND : apt 01...

FIND : apt 02...

FIND : apt 03...

and so on

Thank you!

Answer 1

Here's what you are looking for:

Sub findApt()
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("B:B"))
    Dim i As Integer
    i = 1
    Do While i <= count
        If (InStr(Range("B" & i), "apt")) Then
            Range("B" & i) = Mid(Range("B" & i), 1, Application.WorksheetFunction.Search("apt", Range("B" & i)) - 1) & "app 30"
        End If
        i = i + 1
    Loop
End Sub

This assumes that your data is in column B. Enjoy!