Find/Replace in Module

[Matt]

Hey all,

Well it's not Thanksgiving yet...so there's work to be done, ahh ok my
work.

I have spent the last couple of days trying to figure out why my
procedure never
ends, and I still can't seem to figure it out.
My goal is to use the procedure below to Find/Replace a line of code
from the Code Module.

(I've looked at it for a while and it seems simple enough but I've had
my hands involved
in it so it's probably missing a key ingredient...like "if false then,
exit sub, end if").

Here's the code from module 1-->

Sub FindChange()
Dim I As Long
Dim myProject As VBProject
Dim myComponent As VBComponent
Dim myModule As CodeModule
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
Dim strNewDestination As String
Dim strFind As String
strFind = "'BOOKMARK"
strNewDestination = "Set wks = wkb.worksheets(""Sheet3"") 'BOOKMARK"
Set wkb = ThisWorkbook
Set myProject = wkb.VBProject
Set myComponent = myProject.VBComponents("Module2")
Set myModule = myComponent.CodeModule
With myModule
While .Find(strFind, StartLine, StartColumn, EndLine, EndColumn, False,
False, False)
..ReplaceLine StartLine, strNewDestination
StartLine = StartLine + 1
Wend
End With
End Sub

'Here's the code from Module 2 -->

Sub One()
Set wks = wkb.Worksheets("Sheet1") 'BOOKMARK
End Sub

My thanks to anyone able digest this mystery and bring forth new truth
and
understanding to the VBE, VBA's inner sanctum.

MattS

 

[Jim Rech]

I'd guess that the problem is with this line:

strNewDestination = "Set wks = wkb.worksheets(""Sheet3"") 'BOOKMARK"

The comment at the end of the line keeps putting "BOOKMARK" back in the
module.


--
Jim
| Hey all,
|
| Well it's not Thanksgiving yet...so there's work to be done, ahh ok my
| work.
|
| I have spent the last couple of days trying to figure out why my
| procedure never
| ends, and I still can't seem to figure it out.
| My goal is to use the procedure below to Find/Replace a line of code
| from the Code Module.
|
| (I've looked at it for a while and it seems simple enough but I've had
| my hands involved
| in it so it's probably missing a key ingredient...like "if false then,
| exit sub, end if").
|
| Here's the code from module 1-->
|
| Sub FindChange()
| Dim I As Long
| Dim myProject As VBProject
| Dim myComponent As VBComponent
| Dim myModule As CodeModule
| Dim StartLine As Long, StartColumn As Long
| Dim EndLine As Long, EndColumn As Long
| Dim strNewDestination As String
| Dim strFind As String
| strFind = "'BOOKMARK"
| strNewDestination = "Set wks = wkb.worksheets(""Sheet3"") 'BOOKMARK"
| Set wkb = ThisWorkbook
| Set myProject = wkb.VBProject
| Set myComponent = myProject.VBComponents("Module2")
| Set myModule = myComponent.CodeModule
| With myModule
| While .Find(strFind, StartLine, StartColumn, EndLine, EndColumn, False,
| False, False)
| .ReplaceLine StartLine, strNewDestination
| StartLine = StartLine + 1
| Wend
| End With
| End Sub
|
| 'Here's the code from Module 2 -->
|
| Sub One()
| Set wks = wkb.Worksheets("Sheet1") 'BOOKMARK
| End Sub
|
| My thanks to anyone able digest this mystery and bring forth new truth
| and
| understanding to the VBE, VBA's inner sanctum.
|
| MattS
|

 

[Matt]

Thanks Jim, but that didn't do it.
The bookmark should be in there.
The code updates the module fine, but then never stops running.

 

[Jason Lepack]

Why does excel tell me that VBProject is a User-Defined type?

Cheers,
Jason Lepack

 

[Jason Lepack]

NM RTFF...

Why does excel tell me that VBProject is a User-Defined type?

Cheers,
Jason Lepack

 

[Jason Lepack]

If the end is less than the start then it seems to me that the .find
starts over at the start of the module. This was created by adding one
to the startline.
You can verify this with this little bit of code and adding another
bookmark.

x = 1
With myModule
While .Find(strFind, StartLine, StartColumn, EndLine,
EndColumn, False, False, False) And x < 3
.ReplaceLine StartLine, strNewDestination
StartLine = StartLine + 1
x = x + 1
Wend
End With

Notice that only the first bookmark gets updated.

The way to correct this is to set the endline to -1 everytime in the
loop.

Public Sub FindChange()
Dim myProject As VBProject
Dim myComponent As VBComponent
Dim myModule As CodeModule
Dim StartLine As Long, StartColumn As Long
Dim EndLine As Long, EndColumn As Long
Dim strNewDestination As String, strFind As String

strFind = "'BOOKMARK"
strNewDestination = "Set wks = wkb.worksheets(""Sheet3"")
'BOOKMARK"

Set wkb = ThisWorkbook
Set myProject = wkb.VBProject
Set myComponent = myProject.VBComponents("Module2")
Set myModule = myComponent.CodeModule
With myModule
While .Find(strFind, StartLine, StartColumn, EndLine,
EndColumn, False, False, False)
.ReplaceLine StartLine, strNewDestination
StartLine = StartLine + 1
EndLine = -1
Wend
End With
End Sub


Cheers,
Jason Lepack