Newbie repeat question
 

Hi,
so I want a parrt of my code to repeat until there is a line format found
on the cell which is already being searched for, my code is below. What am I
missing here?
My goal here is to format a seperation line on a page when the macro finds a
particular word in a column (the word is the end of each section of listing
for a sales person).
The code listed below does what I want but does not stop and continues
indefinately.
All help VERY much appreciated
Patrick


Sub Find2()
Do
Dim word As String
word = "ATM"
Cells.Find(What:=word, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Cells.FindNext(After:=ActiveCell).Activate
Loop Until (LineStyle = xlContinuous)
End Sub

Newbie repeat question
 

Below is code that stores the cell address of the first found text, and
then sets up a loop to do the border stuff and at the end calls the
Findnext method and checks the address of the next found text with the
first.

Sub Myfind()
Dim rng As Range
Dim result As Range
Dim firstAddress As String

Set rng = ThisWorkbook.Worksheets("sheet1").Range("A:F")

With rng
Set result = .Find("ATM", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)

If Not result Is Nothing Then
firstAddress = result.Address 'get address of first
result
Do
With result.EntireRow
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
End With
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <
firstAddress
End If
End With
End Sub

Newbie repeat question
 

Thanks so much for the help. I will try this when I get to work :)
Patrick
"Tony James" wrote in message
oups.com...
Below is code that stores the cell address of the first found text, and
then sets up a loop to do the border stuff and at the end calls the
Findnext method and checks the address of the next found text with the
first.

Sub Myfind()
Dim rng As Range
Dim result As Range
Dim firstAddress As String

Set rng = ThisWorkbook.Worksheets("sheet1").Range("A:F")

With rng
Set result = .Find("ATM", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)

If Not result Is Nothing Then
firstAddress = result.Address 'get address of first
result
Do
With result.EntireRow
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
End With
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <
firstAddress
End If
End With
End Sub

Newbie repeat question
 

I am getting a runtime error on the format part of the code?
Any ideas? I pasted the modified code below which applies to my sheet.
Thanks
Patrick

Sub Myfind()
Dim rng As Range
Dim result As Range
Dim firstAddress As String

Set rng = ThisWorkbook.Worksheets("Analyzer_Patrick's Monthly
Repo").Range("A:F")

With rng
Set result = .Find("ATM", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)

If Not result Is Nothing Then
firstAddress = result.Address
Do
With result.EntireRow
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
End With
Set result = .FindNext(result)
Loop While Not result Is Nothing And result.Address <
firstAddress
End If
End With
End Sub

Newbie repeat question
 

Patrick wrote:
I am getting a runtime error on the format part of the code?
Any ideas?

I can't be sure with the information given.
What is the exact error message and on which line does it occur?

The code works fine for me - Excel 2003 & Windows XP.