我正在開發一個將 Excel 電子表格匯出到 csv 的宏，但是該作業表的單元格具有我想通過將文本添加到它們適用的單元格來識別的格式。對于頂部和左側有邊框的單元格，我想添加一個 | 到單元格文本的開頭。我已經能夠讓 Cells.Replace 與僅在頂部有邊框的空白單元格一起使用，但無法識別其他格式，并且它不適用于任何有內容的單元格，即使我嘗試完全替換內容。
這是我到目前為止所得到的簡化版本，我做錯了什么？
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
uj5u.com熱心網友回復：
通常，您Find()在回圈中使用FindNext()，但使用時似乎這不起作用SearchFormat:=True（請參閱http://www.tushar-mehta.com/publish_train/xl_vba_cases/1001 range find.htm#_Using_the_SearchFormat:~:text=不幸的是, FindNext does not respect the SearchFormat specification )
您可能還想檢查一個單元格是否已經有一個前導“|” 在添加一個之前。
解決 SearchFormat/FindNext 問題的示例方法：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/495908.html