Find every instance of a word, then find previous instance of another word

[cplindem]

I am trying to find every instance of the text "New Group", then go back to the previous instance of the text "Title", then add a border to that entire row.
The code I currently have correctly does this for one instance, but I can't figure out how to get it to move on to the next instance until the end of the worksheet.

Basically I have a random number of cells containing "Title" between other cells containing "New Group". I want to add a border to the row containing "Title" that occurs immediately prior to "New Group" cells. Hope that makes sense.

Presumably I need the GroupRow variable to keep changing (looking at the next instance), but I can't get it to happen correctly. Any ideas?

Sub RowLines()


Dim NewGroup As Range
Dim GroupRow As Long

GroupRow = Range("A:A").Find("New Group", , , xlPart, xlByRows, xlNext).Row

Set NewGroup = Range("A:A").Find("New Group", Range("A" & GroupRow), , xlPart, xlByRows, xlNext)

Range("A:A").Find("Title", NewGroup, , xlPart, xlByRows, xlPrevious, True).EntireRow.Borders(xlEdgeBottom).Weight = xlThin


End Sub

Maybe there is a completely different method that would work better.

[ashleyuk1984]

A sample workbook with some small before and after results, would be most appreciated.
Sometimes it's hard to put across what your actually trying to do.
To you, it makes perfect sense, but for others, it sounds a bit gibberish :)

[cplindem]

Here's a sample:
11492

Before and after tabs are included, as is the above code. If you run it, you will see that a Title row prior to a New Group cell receives a border. I want this to occur for every group.

I also don't quite understand why the current code is working for the 2nd group and not the 1st, but the more important issue is that I want it to work for every group in the worksheet. The actual worksheet has far more groups than I have blocked out here.

Thanks

[ashleyuk1984]

Thanks for providing an example.
Do your titles ALWAYS look like this?? Or will it sometimes be different?


Always like this:



New Group








Title 1


Title 2


Alternate Title 1





Title 3


Title 4


Alternate Title 2




Or could it also be like this






New Group








Title 1


Title 2


Alternate Title 1

[cplindem]

The quantity and exact wording of the titles varies. Sorry, I should have written it that way in the example. They all have the text "title" though.
That's why I was trying to find each instance of "New Group" and then find the previous instance of "Title".

[ashleyuk1984]

Ok, not a problem. Just thinking outside of the box a little, maybe we could do something with the numbers, 1 2 3 4 5 6.

Do the numbers always run in squence, and do they always start straight underneth the last "title"



New Group








Title 1


Title 2


Alternate Title 1





Title 3


Title 4


Alternate Title 2


1


2


3


4


5


6




Maybe we could search for "1", instead of the last "title" and place a line along the top of the row. It would make it look like the line is directly where it should be?

[cplindem]

That's another example of something I should have varied in the uploaded file. What is shown between the titles and "New Group" is never the same. The text in those cells varies, as does the quantity of rows.

[ashleyuk1984]

Ok fair enough, it's safe to say that searching for the last "Title" is the only way of doing it then. I'll try and think of something

[ashleyuk1984]

This seems to do the trick.


Sub Macro1()
Dim LastRow As Integer
Dim NewGroup As Integer
Dim LastTitle As Integer

Range("A1").Select
LastRow = Range("A1048576").End(xlUp).Row

For x = 1 To LastRow

NewGroup = Cells.Find(What:="New Group", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

LastTitle = Cells.Find(What:="Title", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase _
:=True).Row


Rows(LastTitle).Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With

NewGroup = Cells.Find(What:="New Group", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Next x

End Sub

Add Application.Screenupdating, to stop the screen flickering. Makes it look neater as well.

[cplindem]

Beautiful. I didn't think to run a new search at the end of the loop. That works well.

One part I don't quite understand is why does the NewGroup variable always = -1?

[ashleyuk1984]

Because it's not actually suppose to be there :P lol. I forgot to take it out.
You should be fine just to remove it.

The loop itself is totally wrong, I'm surprised it actually worked.
But I didn't bother changing it because the end result is correct. :)

[cplindem]

Ha, I was wondering about that.

So the loop only works because "x To LastRow" happens to be enough iterations to find every instance. I suppose there's probably a more efficient loop.



EDIT: Here's my slightly more efficient loop solution. Instead of using LastRow I used a TotalGroups variable, where TotalGroups equals a COUNTIF of column A for "New Group".
This way the loop of "x To TotalGroups" is a much smaller loop (it was causing problems in my actual data set because it's so large).

Many thanks for your help.

[david000]

Beautiful. I didn't think to run a new search at the end of the loop. That works well.

One part I don't quite understand is why does the NewGroup variable always = -1?


The variable NewGroup was Boolean not an integer. You can substitute that Find for an If and speed it up considerably.





Sub Macro1()
Dim LastRow As Integer
Dim LastTitle As Integer
LastRow = Range("A1048576").End(xlUp).Row
For x = 1 To LastRow
If Cells(x, 1) = "New Group" Then
LastTitle = Cells.Find(What:="Title", After:=Cells(x, 1), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase _
:=True).Row
With Range("a" & LastTitle).EntireRow.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End If
Next x
End Sub

[snb]

Sub M_snb()
For j = 10 To UsedRange.Rows.Count Step 20
Cells(j, 1).Resize(, 6).Borders(9).LineStyle = 1
Cells(j, 1).Resize(, 6).Borders(9).Weight = 2
Next
End Sub

or


Sub M_snb()
Columns(1).SpecialCells(4).EntireRow.Delete
For Each cl In Cells(1).CurrentRegion.Columns(2).SpecialCells(4)
If cl.Row > 1 Then
With cl.Offset(-(cl.Row - y) \ 2 - 1, -1).Resize(, 6).Borders(9)
.LineStyle = 1
.Weight = 3
End With
End If
y = cl.Row
Next
End Sub

[cplindem]

The variable NewGroup was Boolean not an integer.
Interesting. I forgot that True = -1 for Booleans.

Thanks to everyone for the assistance and alternate suggestions. Much appreciated.