Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Sep 2002
    Location
    Toronto, Ontario, Canada
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Repeat Find (2002)

    Hi,

    I am very novice with macros (I can record keystrokes!). I am currently working on a project at work that requires one. I can get the functionality working (Find the word "Sub-total"; insert a row and format the row), but I can't seem to make it repeat for all occurrences of the word Sub-total. (I can get it to occur once, and I can get it to loop and never stop!).

    Can someone please provide the necessary command to have a series of command repeat on find for all occurrences of the word - without starting at the top of the document?

    Here's what I have so far:

    Cells.Find(What:="Sub-total", After:=ActiveCell, LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    Selection.Font.Bold = True
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.EntireRow.Insert
    ActiveCell.Range("A1:G1").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    ActiveCell.Offset(1, 0).Range("A1").Select


    Thank you so much!
    Diana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeat Find (2002)

    This is rather tricky since your code inserts a row each time. This 'resets' the Find object. Will the Sub-total text be in a specific column or set of columns?

  3. #3
    Lounger
    Join Date
    Sep 2002
    Location
    Toronto, Ontario, Canada
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat Find (2002)

    The word 'sub-total' is found in column A only. However, depending on the document, there could be 10 occurrences of the word, 20 occurrences or even 40! Each document will be different - there is no set number.

    Ideally, the macro should find the word.... add the row.... and then find the next occurrence...add the row, etc. until it gets to the end of column A.

    Thanks!
    Diana

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Repeat Find (2002)

    Try this:
    <code>
    Sub Test()
    Dim oCell As Range
    Dim strAddress As String
    With Range("A:A")
    Set oCell = .Range("A1")
    Set oCell = .Find(What:="Sub-total", After:=oCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    If Not oCell Is Nothing Then
    strAddress = oCell.Address
    Do
    oCell.Font.Bold = True
    oCell.Offset(1, 0).EntireRow.Insert
    With oCell.Offset(1, 0).Resize(1, 7)
    .Borders.LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    With .Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    End With
    Set oCell = .Find(What:="Sub-total", After:=oCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    Loop Until oCell.Address = strAddress
    End If
    End With
    End Sub
    </code>
    The code loops until it returns to the first instance of Sub-total, then it quits.
    I rewrote your code slightly to avoid selecting cells.

  5. #5
    Lounger
    Join Date
    Sep 2002
    Location
    Toronto, Ontario, Canada
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Repeat Find (2002)

    It works beautifully! Thanks so much for the macro and your quick reply!
    Diana

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •