Scanning in reverse order

[styoda]

Hi,

I'm incredibly rusty with VB for macro's, and I'm a little lost here.

With this code, which works, it looks for the names "ServerA", "ServerB" etc
and then formats the cells. But I need to insert 2 rows just above
"ServerNM", but when I do this it gets stuck in a loop as the number of rows
have changed and never gets onto the next ServerNM.
The only way I think I can do this is to start from the bottom of the list,
"LastCell_F".

Is it possible to scan from the bottom of the list or anyone have any ideas
as to how I can insert 2 rows just above every "ServerNM"?

Range("F1000").End(xlUp).Offset(0, 0).Select
LastCell_F = ActiveCell.Row

Dim ServerA As Long
Dim ServerB As Long
Dim ServerC As Long
Dim ServerD As Long
Dim ServerE As Long

For Each ServerNM In Worksheets("Quote").Range("A10:A" & LastCell_F)
ServerA = InStr(1, (ServerNM.Value), "ServerA")
ServerB = InStr(1, (ServerNM.Value), "ServerB")
ServerC = InStr(1, (ServerNM.Value), "ServerC")
ServerD = InStr(1, (ServerNM.Value), "ServerD")
ServerE = InStr(1, (ServerNM.Value), "ServerE")
If ServerA > 0 Or ServerB > 0 Or ServerC > 0 Or ServerD > 0 Or ServerE >
0 Then
ServerNM.Select
Selection.Font.Bold = True
ServerNM.Offset(0, 2).Select
Selection.Font.Bold = True
Selection.HorizontalAlignment = xlCenter
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With

ServerNM.Offset(1, 2).Select
Selection.Font.Italic = True
Selection.Font.Bold = True
ServerNM.Offset(1, 7).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
ServerNM.Offset(0, 7).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
ServerNM.Offset(-1, 7).Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
End If
Next ServerNM

Range("A1").Select

End Sub


Hope someone can help,
Thanks
S

 

[Don Guillett]

Sub findtextinsertrow()
mc = "k"
For i = Cells(Rows.Count, mc).End(xlUp).Row To 2 Step -1
If InStr(Cells(i, mc), "server") Then Rows(i).Insert
Next i
End Sub

Methinks your code could be greatly simlified. Are there Servers other than
A-E
If desired, send your file to my address below

 

[Joel]

I cleaned up your code and made the additional change. I changed the For
loop to a do loop and started at the last row. You need to do this when
inserting rows. At the end of the loop I inserted two rows and then
decremented the row counter by 3 to get to the previous row with data.

Sub formatServers()

Dim ServerA As Long
Dim ServerB As Long
Dim ServerC As Long
Dim ServerD As Long
Dim ServerE As Long

LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
RowCount = LastCell

Do While RowCount >= 10
Set ServerNM = Range("A" & RowCount)

ServerA = InStr(1, (ServerNM.Value), "ServerA")
ServerB = InStr(1, (ServerNM.Value), "ServerB")
ServerC = InStr(1, (ServerNM.Value), "ServerC")
ServerD = InStr(1, (ServerNM.Value), "ServerD")
ServerE = InStr(1, (ServerNM.Value), "ServerE")

If ServerA > 0 Or ServerB > 0 Or ServerC > 0 Or _
ServerD > 0 Or ServerE > 0 Then

With ServerNM
.Font.Bold = True
.Offset(0, 2).Font.Bold = True
.HorizontalAlignment = xlCenter
With .Interior
.ColorIndex = 37
.Pattern = xlSolid
End With

With .Offset(1, 2).Font
.Italic = True
.Bold = True
End With

With .Offset(1, 7).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

With .Offset(0, 7).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With

With .Offset(1, 7).Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
End With
End If

Rows(RowCount).Insert
Rows(RowCount).Insert

RowCount = RowCount - 3
Loop
End Sub

 

[Joel]

I lost the minus sign on the 2nd time this line is used

from
With .Offset(1, 7).Interior

to
With .Offset(-1, 7).Interior

 

[styoda]

Thanks Joel,

I made a few adjustment but this works perfectly.

Cheers
S