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.
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.