Find and replace, or insert in VBA code

[keri]

Hi,

I have an update program used to change or add to the code in another
workbook. I have previously used it to correct errors as below;

On Error GoTo thiserror:
With THISBOOK.VBProject.VBComponents.Item("module1").CodeModule

Dim MYCODE
MYCODE = .Lines(1, .CountOfLines)
MYCODE = Replace(MYCODE, "SOUTH", "South")

..DeleteLines 1, .CountOfLines
..InsertLines .CountOfLines + 1, MYCODE

End With

Here it finds the word SOUTH in the code and replaces it with the word
South. However I now wish to make this more complex and am unsure how
to go about it.

I want to find the line -

Next Target

and insert the following code below it -

For Each Target In ActiveSheet.Range("B3:B" & ROWSS + 2)
If Target.Offset(0, 10).Value = "Closed" Then
With Target
.Offset(0, 1).Value = "-1"
.Interior.ColorIndex = 40
.Offset(0, 2).Interior.ColorIndex = 40
.Offset(0, 3).Interior.ColorIndex = 40
.Offset(0, 4).Interior.ColorIndex = 40
.Offset(0, 5).Interior.ColorIndex = 40
.Offset(0, 6).Interior.ColorIndex = 40
.Offset(0, 7).Interior.ColorIndex = 40
.Offset(0, 8).Interior.ColorIndex = 40
.Offset(0, 9).Interior.ColorIndex = 40
.Offset(0, 10).Interior.ColorIndex = 40
.Offset(0, 11).Interior.ColorIndex = 40
.Offset(0, 12).Interior.ColorIndex = 40
.Offset(0, 13).Interior.ColorIndex = 40
.Offset(0, 14).Interior.ColorIndex = 40
.Offset(0, 15).Interior.ColorIndex = 40
End With
Else
If Target.Offset(0, 10).Value = "Duplicate" Then
With Target
.Offset(0, 1).Value = "-1"
.Interior.ColorIndex = 40
.Offset(0, 2).Interior.ColorIndex = 40
.Offset(0, 3).Interior.ColorIndex = 40
.Offset(0, 4).Interior.ColorIndex = 40
.Offset(0, 5).Interior.ColorIndex = 40
.Offset(0, 6).Interior.ColorIndex = 40
.Offset(0, 7).Interior.ColorIndex = 40
.Offset(0, 8).Interior.ColorIndex = 40
.Offset(0, 9).Interior.ColorIndex = 40
.Offset(0, 10).Interior.ColorIndex = 40
.Offset(0, 11).Interior.ColorIndex = 40
.Offset(0, 12).Interior.ColorIndex = 40
.Offset(0, 13).Interior.ColorIndex = 40
.Offset(0, 14).Interior.ColorIndex = 40
.Offset(0, 15).Interior.ColorIndex = 40
End With
End If
End If
Next Target
ActiveSheet.Range("b2:t3000").Select
Selection.sort Key1:=ActiveSheet.Range("C3"), Order1:=xlDescending



But I am unsure where to start.

Many thanks in advance

 

[Jim Rech]

I'd start by replacing all of this:

.Offset(0, 2).Interior.ColorIndex = 40
.Offset(0, 3).Interior.ColorIndex = 40
.Offset(0, 4).Interior.ColorIndex = 40
.Offset(0, 5).Interior.ColorIndex = 40
.Offset(0, 6).Interior.ColorIndex = 40
.Offset(0, 7).Interior.ColorIndex = 40
.Offset(0, 8).Interior.ColorIndex = 40
.Offset(0, 9).Interior.ColorIndex = 40
.Offset(0, 10).Interior.ColorIndex = 40
.Offset(0, 11).Interior.ColorIndex = 40
.Offset(0, 12).Interior.ColorIndex = 40
.Offset(0, 13).Interior.ColorIndex = 40
.Offset(0, 14).Interior.ColorIndex = 40
.Offset(0, 15).Interior.ColorIndex = 40

with

.Offset(0, 2).Resize(1, 14).Interior.ColorIndex = 40



--
Jim
| Hi,
|
| I have an update program used to change or add to the code in another
| workbook. I have previously used it to correct errors as below;
|
| On Error GoTo thiserror:
| With THISBOOK.VBProject.VBComponents.Item("module1").CodeModule
|
| Dim MYCODE
| MYCODE = .Lines(1, .CountOfLines)
| MYCODE = Replace(MYCODE, "SOUTH", "South")
|
| .DeleteLines 1, .CountOfLines
| .InsertLines .CountOfLines + 1, MYCODE
|
| End With
|
| Here it finds the word SOUTH in the code and replaces it with the word
| South. However I now wish to make this more complex and am unsure how
| to go about it.
|
| I want to find the line -
|
| Next Target
|
| and insert the following code below it -
|
| For Each Target In ActiveSheet.Range("B3:B" & ROWSS + 2)
| If Target.Offset(0, 10).Value = "Closed" Then
| With Target
| .Offset(0, 1).Value = "-1"
| .Interior.ColorIndex = 40
| .Offset(0, 2).Interior.ColorIndex = 40
| .Offset(0, 3).Interior.ColorIndex = 40
| .Offset(0, 4).Interior.ColorIndex = 40
| .Offset(0, 5).Interior.ColorIndex = 40
| .Offset(0, 6).Interior.ColorIndex = 40
| .Offset(0, 7).Interior.ColorIndex = 40
| .Offset(0, 8).Interior.ColorIndex = 40
| .Offset(0, 9).Interior.ColorIndex = 40
| .Offset(0, 10).Interior.ColorIndex = 40
| .Offset(0, 11).Interior.ColorIndex = 40
| .Offset(0, 12).Interior.ColorIndex = 40
| .Offset(0, 13).Interior.ColorIndex = 40
| .Offset(0, 14).Interior.ColorIndex = 40
| .Offset(0, 15).Interior.ColorIndex = 40
| End With
| Else
| If Target.Offset(0, 10).Value = "Duplicate" Then
| With Target
| .Offset(0, 1).Value = "-1"
| .Interior.ColorIndex = 40
| .Offset(0, 2).Interior.ColorIndex = 40
| .Offset(0, 3).Interior.ColorIndex = 40
| .Offset(0, 4).Interior.ColorIndex = 40
| .Offset(0, 5).Interior.ColorIndex = 40
| .Offset(0, 6).Interior.ColorIndex = 40
| .Offset(0, 7).Interior.ColorIndex = 40
| .Offset(0, 8).Interior.ColorIndex = 40
| .Offset(0, 9).Interior.ColorIndex = 40
| .Offset(0, 10).Interior.ColorIndex = 40
| .Offset(0, 11).Interior.ColorIndex = 40
| .Offset(0, 12).Interior.ColorIndex = 40
| .Offset(0, 13).Interior.ColorIndex = 40
| .Offset(0, 14).Interior.ColorIndex = 40
| .Offset(0, 15).Interior.ColorIndex = 40
| End With
| End If
| End If
| Next Target
| ActiveSheet.Range("b2:t3000").Select
| Selection.sort Key1:=ActiveSheet.Range("C3"), Order1:=xlDescending
|
|
|
| But I am unsure where to start.
|
| Many thanks in advance
|

 

[keri]

You've probably just saved me a million lines of code in my projects.

I have never heard of or seen used the resize command used....can
anyone give me an explantion?

(I guess it means resize the offset area to (0,1)0,14))


Offset(0, 2).Resize(1, 14).Interior.ColorIndex = 40

Any help on the find and insert . replace function in VB would also be
very much appreciated.

Thanks, K