ExcelBanter - Scanning in reverse order

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Scanning in reverse order (https://www.excelbanter.com/excel-programming/429370-scanning-reverse-order.html)

styoda

Scanning in reverse order

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

Scanning in reverse order

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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"styoda" wrote in message
...
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

joel

Scanning in reverse order

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

"styoda" wrote:

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

joel

Scanning in reverse order

joel

Scanning in reverse order

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

"Joel" wrote:

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

"styoda" wrote:

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

joel

Scanning in reverse order

styoda

Scanning in reverse order

Thanks Joel,

I made a few adjustment but this works perfectly.

Cheers
S

"Joel" wrote:

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

"Joel" wrote:

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

"styoda" wrote:

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

All times are GMT +1. The time now is 03:45 PM.