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
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%20range%20find.htm#_Using_the_SearchFormat:~:text=Unfortunately%2C%20FindNext%20does%20not%20respect%20the%20SearchFormat%20specification)
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