Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Newbie Formula Question - how to get formula to repeat in each subsequent row? | New Users to Excel | |||
Real Newbie newbie question | New Users to Excel | |||
If Then Question from Newbie. . . | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Newbie With A Question | Excel Worksheet Functions |