Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
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 "Matt" wrote in message oups.com... | 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 | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
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. On Nov 21, 11:59 am, "Jim Rech" wrote: 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 themodule. -- Jim"Matt" wrote in ooglegroups.com... | 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 toFind/Replacea line of code | from the CodeModule. | | (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 frommodule1-- | | 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 fromModule2 -- | | 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 | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
Why does excel tell me that VBProject is a User-Defined type?
Cheers, Jason Lepack Matt wrote: 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. On Nov 21, 11:59 am, "Jim Rech" wrote: 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 themodule. -- Jim"Matt" wrote in ooglegroups.com... | 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 toFind/Replacea line of code | from the CodeModule. | | (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 frommodule1-- | | 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 fromModule2 -- | | 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 | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
NM RTFF...
Jason Lepack wrote: Why does excel tell me that VBProject is a User-Defined type? Cheers, Jason Lepack Matt wrote: 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. On Nov 21, 11:59 am, "Jim Rech" wrote: 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 themodule. -- Jim"Matt" wrote in ooglegroups.com... | 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 toFind/Replacea line of code | from the CodeModule. | | (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 frommodule1-- | | 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 fromModule2 -- | | 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 | |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/Replace in Module
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 Jason Lepack wrote: NM RTFF... Jason Lepack wrote: Why does excel tell me that VBProject is a User-Defined type? Cheers, Jason Lepack Matt wrote: 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. On Nov 21, 11:59 am, "Jim Rech" wrote: 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 themodule. -- Jim"Matt" wrote in ooglegroups.com... | 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 toFind/Replacea line of code | from the CodeModule. | | (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 frommodule1-- | | 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 fromModule2 -- | | 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 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace module with code | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
Replace method - cannot find any data to replace | Excel Programming | |||
Macro to replace a VBA module? | Excel Programming |