Changing Code Modules, PLEASE HELP ASAP!! PART 2

[Brett Smith]

I posted a question before and it was answered for the most part. I need to
replace 6 strings all at once, but my code only replaces 2 of the 6
variables. How can I change all 6? Here is the code below. Any help would
be greatly appreciated. Thanks!!

Sub FileSearchforMacros2()

Dim i As Integer
Dim wkbkOne As Workbook

With Application.FileSearch
.LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
'MsgBox "There were " & .FoundFiles.Count & _
' "file(s) found."
For i = 1 To .FoundFiles.Count
'MsgBox .FoundFiles(i)
Set wkbkOne = Application.Workbooks.Open( _
.FoundFiles(i), , , , Password:=("INGRAM"))
ReplacecodeInModule wkbkOne
remove_xlfit3_ref wkbkOne
wkbkOne.Save
wkbkOne.Close
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub



------------------------------------------------------------------------------------------
Sub ReplacecodeInModule(RepWk As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")

For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(i)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(i), myRStr(i))
'MsgBox myCode

.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub

[Brett Smith]

Oops, please take a look at this code as a replacement of ReplacecodeInModule
Sub below.


Sub ReplacecodeInModule(RepWk As Workbook, myFStr As Variant, myRStr As
Variant)
Dim myCode As String
'Dim myFStr As Variant
'Dim myRStr As Variant
Dim myMod As VBComponent

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")

For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule

If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(i)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(i), myRStr(i))
'MsgBox myCode

.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
End With
Next myMod
End Sub


"Brett Smith" wrote:

> I posted a question before and it was answered for the most part. I need to
> replace 6 strings all at once, but my code only replaces 2 of the 6
> variables. How can I change all 6? Here is the code below. Any help would
> be greatly appreciated. Thanks!!
>
> Sub FileSearchforMacros2()
>
> Dim i As Integer
> Dim wkbkOne As Workbook
>
> With Application.FileSearch
> .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> .SearchSubFolders = True
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> 'MsgBox "There were " & .FoundFiles.Count & _
> ' "file(s) found."
> For i = 1 To .FoundFiles.Count
> 'MsgBox .FoundFiles(i)
> Set wkbkOne = Application.Workbooks.Open( _
> .FoundFiles(i), , , , Password:=("INGRAM"))
> ReplacecodeInModule wkbkOne
> remove_xlfit3_ref wkbkOne
> wkbkOne.Save
> wkbkOne.Close
> Next i
> Else
> MsgBox "There were no files found."
> End If
> End With
> End Sub
>
>
>
> ------------------------------------------------------------------------------------------
> Sub ReplacecodeInModule(RepWk As Workbook)
> Dim myCode As String
> Dim myFStr As Variant
> Dim myRStr As Variant
> Dim myMod As VBComponent
>
> myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
>
> For Each myMod In RepWk.VBProject.VBComponents
> With myMod.CodeModule
>
> If .CountOfLines > 0 Then
> myCode = .Lines(1, .CountOfLines)
> If InStr(1, myCode, myFStr(i)) > 0 Then
> 'MsgBox myCode
> myCode = Replace(myCode, myFStr(i), myRStr(i))
> 'MsgBox myCode
>
> .DeleteLines 1, .CountOfLines
> .InsertLines .CountOfLines + 1, myCode
> End If
> End If
> End With
> Next myMod
> End Sub

[Dave Peterson]

I'm surprised that it did two.

This is untested, but it did compile.

Option Explicit
Sub ReplacecodeInModule(RepWk As Workbook)
Dim myCode As String
Dim myFStr As Variant
Dim myRStr As Variant
Dim myMod As VBComponent
Dim iCtr As Long

myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
"XLFitExcludePoint", "XLFitIncludePoint", "YRange")
myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
"xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")


For Each myMod In RepWk.VBProject.VBComponents
With myMod.CodeModule
For iCtr = LBound(myFStr) To UBound(myFStr)
If .CountOfLines > 0 Then
myCode = .Lines(1, .CountOfLines)
If InStr(1, myCode, myFStr(iCtr)) > 0 Then
'MsgBox myCode
myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
'MsgBox myCode
.DeleteLines 1, .CountOfLines
.InsertLines .CountOfLines + 1, myCode
End If
End If
Next iCtr
End With
Next myMod
End Sub

And just to remember, your instr() is case sensitive as written and so is your
replace().

That might be important.


Brett Smith wrote:
>
> I posted a question before and it was answered for the most part. I need to
> replace 6 strings all at once, but my code only replaces 2 of the 6
> variables. How can I change all 6? Here is the code below. Any help would
> be greatly appreciated. Thanks!!
>
> Sub FileSearchforMacros2()
>
> Dim i As Integer
> Dim wkbkOne As Workbook
>
> With Application.FileSearch
> .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> .SearchSubFolders = True
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute() > 0 Then
> 'MsgBox "There were " & .FoundFiles.Count & _
> ' "file(s) found."
> For i = 1 To .FoundFiles.Count
> 'MsgBox .FoundFiles(i)
> Set wkbkOne = Application.Workbooks.Open( _
> .FoundFiles(i), , , , Password:=("INGRAM"))
> ReplacecodeInModule wkbkOne
> remove_xlfit3_ref wkbkOne
> wkbkOne.Save
> wkbkOne.Close
> Next i
> Else
> MsgBox "There were no files found."
> End If
> End With
> End Sub
>
>
>
> ------------------------------------------------------------------------------------------
> Sub ReplacecodeInModule(RepWk As Workbook)
> Dim myCode As String
> Dim myFStr As Variant
> Dim myRStr As Variant
> Dim myMod As VBComponent
>
> myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
>
> For Each myMod In RepWk.VBProject.VBComponents
> With myMod.CodeModule
>
> If .CountOfLines > 0 Then
> myCode = .Lines(1, .CountOfLines)
> If InStr(1, myCode, myFStr(i)) > 0 Then
> 'MsgBox myCode
> myCode = Replace(myCode, myFStr(i), myRStr(i))
> 'MsgBox myCode
>
> .DeleteLines 1, .CountOfLines
> .InsertLines .CountOfLines + 1, myCode
> End If
> End If
> End With
> Next myMod
> End Sub

--

Dave Peterson

[Brett Smith]

I still wasn't able to get it to replace all of the text. Is there a reason
why it is not replacing the last 3 or 4 variable strings in the array?

"Dave Peterson" wrote:

> I'm surprised that it did two.
>
> This is untested, but it did compile.
>
> Option Explicit
> Sub ReplacecodeInModule(RepWk As Workbook)
> Dim myCode As String
> Dim myFStr As Variant
> Dim myRStr As Variant
> Dim myMod As VBComponent
> Dim iCtr As Long
>
> myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
> "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
> "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
>
>
> For Each myMod In RepWk.VBProject.VBComponents
> With myMod.CodeModule
> For iCtr = LBound(myFStr) To UBound(myFStr)
> If .CountOfLines > 0 Then
> myCode = .Lines(1, .CountOfLines)
> If InStr(1, myCode, myFStr(iCtr)) > 0 Then
> 'MsgBox myCode
> myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
> 'MsgBox myCode
> .DeleteLines 1, .CountOfLines
> .InsertLines .CountOfLines + 1, myCode
> End If
> End If
> Next iCtr
> End With
> Next myMod
> End Sub
>
> And just to remember, your instr() is case sensitive as written and so is your
> replace().
>
> That might be important.
>
>
> Brett Smith wrote:
> >
> > I posted a question before and it was answered for the most part. I need to
> > replace 6 strings all at once, but my code only replaces 2 of the 6
> > variables. How can I change all 6? Here is the code below. Any help would
> > be greatly appreciated. Thanks!!
> >
> > Sub FileSearchforMacros2()
> >
> > Dim i As Integer
> > Dim wkbkOne As Workbook
> >
> > With Application.FileSearch
> > .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> > .SearchSubFolders = True
> > .FileType = msoFileTypeExcelWorkbooks
> > If .Execute() > 0 Then
> > 'MsgBox "There were " & .FoundFiles.Count & _
> > ' "file(s) found."
> > For i = 1 To .FoundFiles.Count
> > 'MsgBox .FoundFiles(i)
> > Set wkbkOne = Application.Workbooks.Open( _
> > .FoundFiles(i), , , , Password:=("INGRAM"))
> > ReplacecodeInModule wkbkOne
> > remove_xlfit3_ref wkbkOne
> > wkbkOne.Save
> > wkbkOne.Close
> > Next i
> > Else
> > MsgBox "There were no files found."
> > End If
> > End With
> > End Sub
> >
> >
> >
> > ------------------------------------------------------------------------------------------
> > Sub ReplacecodeInModule(RepWk As Workbook)
> > Dim myCode As String
> > Dim myFStr As Variant
> > Dim myRStr As Variant
> > Dim myMod As VBComponent
> >
> > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> >
> > For Each myMod In RepWk.VBProject.VBComponents
> > With myMod.CodeModule
> >
> > If .CountOfLines > 0 Then
> > myCode = .Lines(1, .CountOfLines)
> > If InStr(1, myCode, myFStr(i)) > 0 Then
> > 'MsgBox myCode
> > myCode = Replace(myCode, myFStr(i), myRStr(i))
> > 'MsgBox myCode
> >
> > .DeleteLines 1, .CountOfLines
> > .InsertLines .CountOfLines + 1, myCode
> > End If
> > End If
> > End With
> > Next myMod
> > End Sub
>
> --
>
> Dave Peterson
>

[Dave Peterson]

Did the elements of the myFstr match the code--including upper/lower case?

Brett Smith wrote:
>
> I still wasn't able to get it to replace all of the text. Is there a reason
> why it is not replacing the last 3 or 4 variable strings in the array?
>
> "Dave Peterson" wrote:
>
> > I'm surprised that it did two.
> >
> > This is untested, but it did compile.
> >
> > Option Explicit
> > Sub ReplacecodeInModule(RepWk As Workbook)
> > Dim myCode As String
> > Dim myFStr As Variant
> > Dim myRStr As Variant
> > Dim myMod As VBComponent
> > Dim iCtr As Long
> >
> > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
> > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
> > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> >
> >
> > For Each myMod In RepWk.VBProject.VBComponents
> > With myMod.CodeModule
> > For iCtr = LBound(myFStr) To UBound(myFStr)
> > If .CountOfLines > 0 Then
> > myCode = .Lines(1, .CountOfLines)
> > If InStr(1, myCode, myFStr(iCtr)) > 0 Then
> > 'MsgBox myCode
> > myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
> > 'MsgBox myCode
> > .DeleteLines 1, .CountOfLines
> > .InsertLines .CountOfLines + 1, myCode
> > End If
> > End If
> > Next iCtr
> > End With
> > Next myMod
> > End Sub
> >
> > And just to remember, your instr() is case sensitive as written and so is your
> > replace().
> >
> > That might be important.
> >
> >
> > Brett Smith wrote:
> > >
> > > I posted a question before and it was answered for the most part. I need to
> > > replace 6 strings all at once, but my code only replaces 2 of the 6
> > > variables. How can I change all 6? Here is the code below. Any help would
> > > be greatly appreciated. Thanks!!
> > >
> > > Sub FileSearchforMacros2()
> > >
> > > Dim i As Integer
> > > Dim wkbkOne As Workbook
> > >
> > > With Application.FileSearch
> > > .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> > > .SearchSubFolders = True
> > > .FileType = msoFileTypeExcelWorkbooks
> > > If .Execute() > 0 Then
> > > 'MsgBox "There were " & .FoundFiles.Count & _
> > > ' "file(s) found."
> > > For i = 1 To .FoundFiles.Count
> > > 'MsgBox .FoundFiles(i)
> > > Set wkbkOne = Application.Workbooks.Open( _
> > > .FoundFiles(i), , , , Password:=("INGRAM"))
> > > ReplacecodeInModule wkbkOne
> > > remove_xlfit3_ref wkbkOne
> > > wkbkOne.Save
> > > wkbkOne.Close
> > > Next i
> > > Else
> > > MsgBox "There were no files found."
> > > End If
> > > End With
> > > End Sub
> > >
> > >
> > >
> > > ------------------------------------------------------------------------------------------
> > > Sub ReplacecodeInModule(RepWk As Workbook)
> > > Dim myCode As String
> > > Dim myFStr As Variant
> > > Dim myRStr As Variant
> > > Dim myMod As VBComponent
> > >
> > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> > >
> > > For Each myMod In RepWk.VBProject.VBComponents
> > > With myMod.CodeModule
> > >
> > > If .CountOfLines > 0 Then
> > > myCode = .Lines(1, .CountOfLines)
> > > If InStr(1, myCode, myFStr(i)) > 0 Then
> > > 'MsgBox myCode
> > > myCode = Replace(myCode, myFStr(i), myRStr(i))
> > > 'MsgBox myCode
> > >
> > > .DeleteLines 1, .CountOfLines
> > > .InsertLines .CountOfLines + 1, myCode
> > > End If
> > > End If
> > > End With
> > > Next myMod
> > > End Sub
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Edward Ulle]

Brett,

I must be missing something. Where is variable i set to a value.

If InStr(1, myCode, myFStr(i)) > 0 Then

Only the first comparison is made, are you sure the second comparison is
made?



*** Sent via Developersdex http://www.developersdex.com ***

[Edward Ulle]

Are you using Option Explicit? Always a good thing to do.


*** Sent via Developersdex http://www.developersdex.com ***

[Brett Smith]

Do you think Option Explicit could be the reason why my program is not
working as planned? I was thinking about that actually.

"Edward Ulle" wrote:

> Are you using Option Explicit? Always a good thing to do.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

[Brett Smith]

Hi Dave,
Not sure what you mean by your question. Are you asking if the program
caught the case specific variables in the code?

"Dave Peterson" wrote:

> Did the elements of the myFstr match the code--including upper/lower case?
>
> Brett Smith wrote:
> >
> > I still wasn't able to get it to replace all of the text. Is there a reason
> > why it is not replacing the last 3 or 4 variable strings in the array?
> >
> > "Dave Peterson" wrote:
> >
> > > I'm surprised that it did two.
> > >
> > > This is untested, but it did compile.
> > >
> > > Option Explicit
> > > Sub ReplacecodeInModule(RepWk As Workbook)
> > > Dim myCode As String
> > > Dim myFStr As Variant
> > > Dim myRStr As Variant
> > > Dim myMod As VBComponent
> > > Dim iCtr As Long
> > >
> > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
> > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
> > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> > >
> > >
> > > For Each myMod In RepWk.VBProject.VBComponents
> > > With myMod.CodeModule
> > > For iCtr = LBound(myFStr) To UBound(myFStr)
> > > If .CountOfLines > 0 Then
> > > myCode = .Lines(1, .CountOfLines)
> > > If InStr(1, myCode, myFStr(iCtr)) > 0 Then
> > > 'MsgBox myCode
> > > myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
> > > 'MsgBox myCode
> > > .DeleteLines 1, .CountOfLines
> > > .InsertLines .CountOfLines + 1, myCode
> > > End If
> > > End If
> > > Next iCtr
> > > End With
> > > Next myMod
> > > End Sub
> > >
> > > And just to remember, your instr() is case sensitive as written and so is your
> > > replace().
> > >
> > > That might be important.
> > >
> > >
> > > Brett Smith wrote:
> > > >
> > > > I posted a question before and it was answered for the most part. I need to
> > > > replace 6 strings all at once, but my code only replaces 2 of the 6
> > > > variables. How can I change all 6? Here is the code below. Any help would
> > > > be greatly appreciated. Thanks!!
> > > >
> > > > Sub FileSearchforMacros2()
> > > >
> > > > Dim i As Integer
> > > > Dim wkbkOne As Workbook
> > > >
> > > > With Application.FileSearch
> > > > .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> > > > .SearchSubFolders = True
> > > > .FileType = msoFileTypeExcelWorkbooks
> > > > If .Execute() > 0 Then
> > > > 'MsgBox "There were " & .FoundFiles.Count & _
> > > > ' "file(s) found."
> > > > For i = 1 To .FoundFiles.Count
> > > > 'MsgBox .FoundFiles(i)
> > > > Set wkbkOne = Application.Workbooks.Open( _
> > > > .FoundFiles(i), , , , Password:=("INGRAM"))
> > > > ReplacecodeInModule wkbkOne
> > > > remove_xlfit3_ref wkbkOne
> > > > wkbkOne.Save
> > > > wkbkOne.Close
> > > > Next i
> > > > Else
> > > > MsgBox "There were no files found."
> > > > End If
> > > > End With
> > > > End Sub
> > > >
> > > >
> > > >
> > > > ------------------------------------------------------------------------------------------
> > > > Sub ReplacecodeInModule(RepWk As Workbook)
> > > > Dim myCode As String
> > > > Dim myFStr As Variant
> > > > Dim myRStr As Variant
> > > > Dim myMod As VBComponent
> > > >
> > > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> > > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> > > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> > > >
> > > > For Each myMod In RepWk.VBProject.VBComponents
> > > > With myMod.CodeModule
> > > >
> > > > If .CountOfLines > 0 Then
> > > > myCode = .Lines(1, .CountOfLines)
> > > > If InStr(1, myCode, myFStr(i)) > 0 Then
> > > > 'MsgBox myCode
> > > > myCode = Replace(myCode, myFStr(i), myRStr(i))
> > > > 'MsgBox myCode
> > > >
> > > > .DeleteLines 1, .CountOfLines
> > > > .InsertLines .CountOfLines + 1, myCode
> > > > End If
> > > > End If
> > > > End With
> > > > Next myMod
> > > > End Sub
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

[Brett Smith]

Edward,
I never set variable i to any value. I just declared it.

"Edward Ulle" wrote:

> Brett,
>
> I must be missing something. Where is variable i set to a value.
>
> If InStr(1, myCode, myFStr(i)) > 0 Then
>
> Only the first comparison is made, are you sure the second comparison is
> made?
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
>

[Tim Williams]

If you just declare i and never set it to anything then its value will
always be 0

Tim

"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> Edward,
> I never set variable i to any value. I just declared it.
>
> "Edward Ulle" wrote:
>
>> Brett,
>>
>> I must be missing something. Where is variable i set to a value.
>>
>> If InStr(1, myCode, myFStr(i)) > 0 Then
>>
>> Only the first comparison is made, are you sure the second comparison is
>> made?
>>
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>

[Tim Williams]

I think Dave is asking whether the values in your array were correct
(including the case). In otherwords, are you sure the code is checking for
the correct strings in the opened workbook.

Tim


"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> Hi Dave,
> Not sure what you mean by your question. Are you asking if the program
> caught the case specific variables in the code?
>
> "Dave Peterson" wrote:
>
>> Did the elements of the myFstr match the code--including upper/lower
>> case?
>>
>> Brett Smith wrote:
>> >
>> > I still wasn't able to get it to replace all of the text. Is there a
>> > reason
>> > why it is not replacing the last 3 or 4 variable strings in the array?
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > I'm surprised that it did two.
>> > >
>> > > This is untested, but it did compile.
>> > >
>> > > Option Explicit
>> > > Sub ReplacecodeInModule(RepWk As Workbook)
>> > > Dim myCode As String
>> > > Dim myFStr As Variant
>> > > Dim myRStr As Variant
>> > > Dim myMod As VBComponent
>> > > Dim iCtr As Long
>> > >
>> > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
>> > > "XLFitExcludePoint", "XLFitIncludePoint",
>> > > "YRange")
>> > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
>> > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
>> > >
>> > >
>> > > For Each myMod In RepWk.VBProject.VBComponents
>> > > With myMod.CodeModule
>> > > For iCtr = LBound(myFStr) To UBound(myFStr)
>> > > If .CountOfLines > 0 Then
>> > > myCode = .Lines(1, .CountOfLines)
>> > > If InStr(1, myCode, myFStr(iCtr)) > 0 Then
>> > > 'MsgBox myCode
>> > > myCode = Replace(myCode, myFStr(iCtr),
>> > > myRStr(iCtr))
>> > > 'MsgBox myCode
>> > > .DeleteLines 1, .CountOfLines
>> > > .InsertLines .CountOfLines + 1, myCode
>> > > End If
>> > > End If
>> > > Next iCtr
>> > > End With
>> > > Next myMod
>> > > End Sub
>> > >
>> > > And just to remember, your instr() is case sensitive as written and
>> > > so is your
>> > > replace().
>> > >
>> > > That might be important.
>> > >
>> > >
>> > > Brett Smith wrote:
>> > > >
>> > > > I posted a question before and it was answered for the most part.
>> > > > I need to
>> > > > replace 6 strings all at once, but my code only replaces 2 of the 6
>> > > > variables. How can I change all 6? Here is the code below. Any
>> > > > help would
>> > > > be greatly appreciated. Thanks!!
>> > > >
>> > > > Sub FileSearchforMacros2()
>> > > >
>> > > > Dim i As Integer
>> > > > Dim wkbkOne As Workbook
>> > > >
>> > > > With Application.FileSearch
>> > > > .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
>> > > > .SearchSubFolders = True
>> > > > .FileType = msoFileTypeExcelWorkbooks
>> > > > If .Execute() > 0 Then
>> > > > 'MsgBox "There were " & .FoundFiles.Count & _
>> > > > ' "file(s) found."
>> > > > For i = 1 To .FoundFiles.Count
>> > > > 'MsgBox .FoundFiles(i)
>> > > > Set wkbkOne = Application.Workbooks.Open( _
>> > > > .FoundFiles(i), , , , Password:=("INGRAM"))
>> > > > ReplacecodeInModule wkbkOne
>> > > > remove_xlfit3_ref wkbkOne
>> > > > wkbkOne.Save
>> > > > wkbkOne.Close
>> > > > Next i
>> > > > Else
>> > > > MsgBox "There were no files found."
>> > > > End If
>> > > > End With
>> > > > End Sub
>> > > >
>> > > >
>> > > >
>> > > > ------------------------------------------------------------------------------------------
>> > > > Sub ReplacecodeInModule(RepWk As Workbook)
>> > > > Dim myCode As String
>> > > > Dim myFStr As Variant
>> > > > Dim myRStr As Variant
>> > > > Dim myMod As VBComponent
>> > > >
>> > > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
>> > > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
>> > > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
>> > > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
>> > > >
>> > > > For Each myMod In RepWk.VBProject.VBComponents
>> > > > With myMod.CodeModule
>> > > >
>> > > > If .CountOfLines > 0 Then
>> > > > myCode = .Lines(1, .CountOfLines)
>> > > > If InStr(1, myCode, myFStr(i)) > 0 Then
>> > > > 'MsgBox myCode
>> > > > myCode = Replace(myCode, myFStr(i), myRStr(i))
>> > > > 'MsgBox myCode
>> > > >
>> > > > .DeleteLines 1, .CountOfLines
>> > > > .InsertLines .CountOfLines + 1, myCode
>> > > > End If
>> > > > End If
>> > > > End With
>> > > > Next myMod
>> > > > End Sub
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> > >
>>
>> --
>>
>> Dave Peterson
>>

[Tim Williams]

No good reason not to use it. Will catch all undeclared variables and typos
in variable names.

Tim

"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> Do you think Option Explicit could be the reason why my program is not
> working as planned? I was thinking about that actually.
>
> "Edward Ulle" wrote:
>
>> Are you using Option Explicit? Always a good thing to do.
>>
>>
>> *** Sent via Developersdex http://www.developersdex.com ***
>>

[Edward Ulle]

Brett,

I think your problem is with i. You say you declare it but where? Not
in ReplacecodeInModule. If it is declared some where else as a public
variable then the value of i could be anything. The i in the main
module is local to that module not ReplacecodeInModule.

If it is not a public variable then i would be 0 in ReplacecodeInModule
and the comparison would only be performed for the first value in
myFStr(i=0).

Option Explicit requires that all variable be declared. If i had not
been declared outside of ReplacecodeInModule the compiler would have
complained.



*** Sent via Developersdex http://www.developersdex.com ***

[Dave Peterson]

Just to add to Tim's reply:

In your code, there will be a difference between

Brett
BRETT
brETT
bretT
brett

Case matters the way you wrote the code.

Brett Smith wrote:
>
> Hi Dave,
> Not sure what you mean by your question. Are you asking if the program
> caught the case specific variables in the code?
>
> "Dave Peterson" wrote:
>
> > Did the elements of the myFstr match the code--including upper/lower case?
> >
> > Brett Smith wrote:
> > >
> > > I still wasn't able to get it to replace all of the text. Is there a reason
> > > why it is not replacing the last 3 or 4 variable strings in the array?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > I'm surprised that it did two.
> > > >
> > > > This is untested, but it did compile.
> > > >
> > > > Option Explicit
> > > > Sub ReplacecodeInModule(RepWk As Workbook)
> > > > Dim myCode As String
> > > > Dim myFStr As Variant
> > > > Dim myRStr As Variant
> > > > Dim myMod As VBComponent
> > > > Dim iCtr As Long
> > > >
> > > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints", _
> > > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint", _
> > > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> > > >
> > > >
> > > > For Each myMod In RepWk.VBProject.VBComponents
> > > > With myMod.CodeModule
> > > > For iCtr = LBound(myFStr) To UBound(myFStr)
> > > > If .CountOfLines > 0 Then
> > > > myCode = .Lines(1, .CountOfLines)
> > > > If InStr(1, myCode, myFStr(iCtr)) > 0 Then
> > > > 'MsgBox myCode
> > > > myCode = Replace(myCode, myFStr(iCtr), myRStr(iCtr))
> > > > 'MsgBox myCode
> > > > .DeleteLines 1, .CountOfLines
> > > > .InsertLines .CountOfLines + 1, myCode
> > > > End If
> > > > End If
> > > > Next iCtr
> > > > End With
> > > > Next myMod
> > > > End Sub
> > > >
> > > > And just to remember, your instr() is case sensitive as written and so is your
> > > > replace().
> > > >
> > > > That might be important.
> > > >
> > > >
> > > > Brett Smith wrote:
> > > > >
> > > > > I posted a question before and it was answered for the most part. I need to
> > > > > replace 6 strings all at once, but my code only replaces 2 of the 6
> > > > > variables. How can I change all 6? Here is the code below. Any help would
> > > > > be greatly appreciated. Thanks!!
> > > > >
> > > > > Sub FileSearchforMacros2()
> > > > >
> > > > > Dim i As Integer
> > > > > Dim wkbkOne As Workbook
> > > > >
> > > > > With Application.FileSearch
> > > > > .LookIn = "\\rnumsriggap06\Abase401\Restempl\652"
> > > > > .SearchSubFolders = True
> > > > > .FileType = msoFileTypeExcelWorkbooks
> > > > > If .Execute() > 0 Then
> > > > > 'MsgBox "There were " & .FoundFiles.Count & _
> > > > > ' "file(s) found."
> > > > > For i = 1 To .FoundFiles.Count
> > > > > 'MsgBox .FoundFiles(i)
> > > > > Set wkbkOne = Application.Workbooks.Open( _
> > > > > .FoundFiles(i), , , , Password:=("INGRAM"))
> > > > > ReplacecodeInModule wkbkOne
> > > > > remove_xlfit3_ref wkbkOne
> > > > > wkbkOne.Save
> > > > > wkbkOne.Close
> > > > > Next i
> > > > > Else
> > > > > MsgBox "There were no files found."
> > > > > End If
> > > > > End With
> > > > > End Sub
> > > > >
> > > > >
> > > > >
> > > > > ------------------------------------------------------------------------------------------
> > > > > Sub ReplacecodeInModule(RepWk As Workbook)
> > > > > Dim myCode As String
> > > > > Dim myFStr As Variant
> > > > > Dim myRStr As Variant
> > > > > Dim myMod As VBComponent
> > > > >
> > > > > myFStr = Array("XLfit3_", "ExcludePoints", "IncludePoints",
> > > > > "XLFitExcludePoint", "XLFitIncludePoint", "YRange")
> > > > > myRStr = Array("xf4_", "ExcludePoint", "IncludePoint",
> > > > > "xf4_ExcludePoint", "xf4_IncludePoint", "RangeY")
> > > > >
> > > > > For Each myMod In RepWk.VBProject.VBComponents
> > > > > With myMod.CodeModule
> > > > >
> > > > > If .CountOfLines > 0 Then
> > > > > myCode = .Lines(1, .CountOfLines)
> > > > > If InStr(1, myCode, myFStr(i)) > 0 Then
> > > > > 'MsgBox myCode
> > > > > myCode = Replace(myCode, myFStr(i), myRStr(i))
> > > > > 'MsgBox myCode
> > > > >
> > > > > .DeleteLines 1, .CountOfLines
> > > > > .InsertLines .CountOfLines + 1, myCode
> > > > > End If
> > > > > End If
> > > > > End With
> > > > > Next myMod
> > > > > End Sub
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

[Edward Ulle]

Brett,

It would seem to me you need to loop through myFStr to compare with each
value in ReplacecodeInModule the line

If InStr(1, myCode, myFStr(i)) > 0 Then

You say you declare but don't set i. I don't see that in your code.
Unless there is an i declared as a global variable i will be 0 and only
the first value of myFStr will compare. With Option Explicit unless i
is global the compiler would complain about i in ReplacecodeInModule.




*** Sent via Developersdex http://www.developersdex.com ***

[Brett Smith]

Tim, Edward;

Then I see where my problem lies. I missed one of the basic fundamentals of
declaring a variable. You must always declare it with a value, and I did
not. So, I will declare i as = 0, and then maybe the program will work. I
will also throw in Option Explicit.


"Tim Williams" wrote:

> If you just declare i and never set it to anything then its value will
> always be 0
>
> Tim
>
> "Brett Smith" <[email protected]> wrote in message
> news:[email protected]...
> > Edward,
> > I never set variable i to any value. I just declared it.
> >
> > "Edward Ulle" wrote:
> >
> >> Brett,
> >>
> >> I must be missing something. Where is variable i set to a value.
> >>
> >> If InStr(1, myCode, myFStr(i)) > 0 Then
> >>
> >> Only the first comparison is made, are you sure the second comparison is
> >> made?
> >>
> >>
> >>
> >> *** Sent via Developersdex http://www.developersdex.com ***
> >>
>
>
>

[Tim Williams]

Brett,

You're using i as a loop counter when cycling through your arrays of search
strings. Your loop takes care of assigning the values.

I can help you out tomorrow if it's still not working. Drop me a line at
work.

Tim.


"Brett Smith" <[email protected]> wrote in message
news:[email protected]...
> Tim, Edward;
>
> Then I see where my problem lies. I missed one of the basic fundamentals
> of
> declaring a variable. You must always declare it with a value, and I did
> not. So, I will declare i as = 0, and then maybe the program will work.
> I
> will also throw in Option Explicit.
>
>
> "Tim Williams" wrote:
>
>> If you just declare i and never set it to anything then its value will
>> always be 0
>>
>> Tim
>>
>> "Brett Smith" <[email protected]> wrote in message
>> news:[email protected]...
>> > Edward,
>> > I never set variable i to any value. I just declared it.
>> >
>> > "Edward Ulle" wrote:
>> >
>> >> Brett,
>> >>
>> >> I must be missing something. Where is variable i set to a value.
>> >>
>> >> If InStr(1, myCode, myFStr(i)) > 0 Then
>> >>
>> >> Only the first comparison is made, are you sure the second comparison
>> >> is
>> >> made?
>> >>
>> >>
>> >>
>> >> *** Sent via Developersdex http://www.developersdex.com ***
>> >>
>>
>>
>>