Is there a way to prepend text to cell with specific formatting using Excel VBA?-CodePudding

I'm working on a macro that exports an excel spreadsheet to csv, however the sheet has cells with formatting that I'd like to identify by adding text to the cell they apply to. For cells that have a border on the top and left, I'd like to add a | to the beginning of the cell's text. I've been able to get the Cells.Replace to work with blank cells with a border on the top only, but no other formatting is ever recognized, and it doesn't work with any cells that have content, even when I try to replace the content entirely.

Here's a simplified version of what I've got so far, what am I doing wrong?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    
    Application.FindFormat.Clear
    
    With Application.FindFormat.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    End With
    
    With Application.FindFormat.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    End With
    
    'Cells.Find(What:="", SearchFormat:=True).Select
    
    Cells.Replace What:="*", Replacement:="||||", SearchFormat:=True
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
End Sub

CodePudding user response：

Typically you use Find() in a loop with FindNext(), but seems like this won't work when using SearchFormat:=True (see http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001 range find.htm#_Using_the_SearchFormat:~:text=Unfortunately, FindNext does not respect the SearchFormat specification)

You might also want to check whether a cell already has a leading "|" before adding one.

Example approach for working around the SearchFormat/FindNext problem:

Sub SearchFormatExample()
   
    Dim f As Range, addr, rng As Range
    
    With Application.FindFormat
        .Clear
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
    End With
    
    Set rng = ActiveSheet.UsedRange
    
    Set f = rng.Find("*", lookat:=xlPart, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, LookIn:=xlFormulas, _
                     searchformat:=True)
    
    If Not f Is Nothing Then addr = f.Address() 'note the first cell found
    
    Do While Not f Is Nothing
        
        Debug.Print f.Address
        'don't add `|` if already present
        If Not f.Value Like "|*" Then f.Value = "|" & f.Value
        
        'using Find not findNext
        Set f = rng.Find("*", after:=f, lookat:=xlPart, _
                    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    LookIn:=xlFormulas, searchformat:=True)

        If f.Address = addr Then Exit Do 'exit when Find has looped back around
    Loop
    
End Sub