PDA

View Full Version : [SOLVED] Problems using VBA to find and replace within VBA



mrcw
07-13-2017, 02:05 PM
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
07-13-2017, 03:33 PM
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
07-14-2017, 05:55 AM
Leith,

That worked perfectly, thanks a bunch for the help!

Much Gratitude,
Cole

Leith Ross
07-14-2017, 07:03 AM
Hello Cole,

You're welcome.