Problems using VBA to find and replace within VBA

[mrcw]

I want to find "Akorn" and replace it with "Jubilant" in all Macros on the worksheet titled "Jubilant Final"

Sub macrofindreplace()

    Dim VBP As VBIDE.VBProject
    Dim VBC As VBIDE.VBComponent
    Dim SL As Long, EL As Long, SC As Long, EC As Long
    Dim S As String
    Dim Found As Boolean
     
    On Error Resume Next
    Set VBP = Sheets("Jubilant Final").VBProject


    For Each VBC In VBP.VBComponents
        If InStr(1, VBC.name, "Jubilant Final", vbTextCompare) > 0 Then
            With VBC.CodeModule
                SL = 1
                SC = 1
                EL = .CountOfLines
                EC = 999
                Found = .Find("Akorn", SL, SC, EL, EC, True, False, False)
                If Found = True Then
                    S = .Lines(SL, 1)
                    S = Replace(S, "Akorn", "Jubilant", 1, -1, vbTextCompare)
                    .ReplaceLine SL, S
                End If
            End With
        End If
    Next VBC

  End Sub

When I run this code no error messages pop up but nothing happens. Any input is appreciated.

[Leith Ross]

Hello mrcw,

Try this.

Sub macrofindreplace()


    Dim Code    As String
    Dim VBComp  As Object
    Dim VBProj  As Object
    Dim WksName As String
    
        WksName = ThisWorkbook.Worksheets("Jubilant Final").CodeName
        
        Set VBProj = Application.VBE.ActiveVBProject
        
        ' You must use the Code Name of the Worksheet.
        Set VBComp = VBProj.VBComponents(WksName)
        
        With VBComp.CodeModule
            Code = .Lines(1, .CountOfLines)
            Code = Replace(Code, "Akorn", "Jubilant")
            .DeleteLines 1, .CountOfLines
            .InsertLines 1, Code
        End With
        
End Sub

[mrcw]

Leith,

That worked perfectly, thanks a bunch for the help!

Much Gratitude,
Cole

[Leith Ross]

Hello Cole,

You're welcome.