Results 1 to 2 of 2

Thread: Loop Macro

  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I have tried recording multiple macros to search through an Excel spreadsheet for an item and then apply formatting and delete the item (row) when it finds the required text. However, none of them worked, so I've ended up manually recording a long Macro, but every time a new Budget Code gets added, the Macro doesn't include it, so it needs to be re-recorded. Can anyone advise how I can use a Macro to loop as long as it finds certain criteria?

    What it needs to do is as follows:

    1) Find "B/F"
    2) When it finds it, it needs to delete that row plus the following row.
    3) Then in the remaining (current) row, from Column C to Column H, it needs to apply the following formatting:

    Code:
    Selection.Font.Bold = True
        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 = xlDouble
            .Weight = xlThick
            .ColorIndex = xlAutomatic
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Can anyone advise how I construct a loop to do this?

    Many thanks in advance.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    this is just a starting point

    Sub findthething()
    Dim strFindParameter As String
    Dim c As Range

    strFindParameter = InputBox("Enter Code for Find")

    With Selection
    Set c = .find(strFindParameter, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstAddress = c.Address
    Do
    c.Font.Bold = True
    Set c = .FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
    End With

    End Sub
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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