如果名稱包含輸入中的部分文本，我正在嘗試創建將洗掉活動作業簿中的所有作業表的代碼。到目前為止，似乎沒有任何作業，我不知道為什么。
我正在使用這段代碼：
Private Sub CommandButton28_Click()
Dim shName As String
Dim xName As String
Dim xWs As Worksheet
Dim cnt As Integer
shName = Application.InputBox("Enter the specific text:", "Delete sheets", _
ThisWorkbook.ActiveSheet.Name, , , , , 2)
If shName = "" Then Exit Sub
xName = "*" & shName & "*"
' MsgBox xName
Application.DisplayAlerts = False
cnt = 0
For Each xWs In ThisWorkbook.Sheets
If xWs.Name Like xName Then
xWs.Delete
cnt = cnt 1
End If
Next xWs
Application.DisplayAlerts = True
MsgBox "Have deleted " & cnt & " worksheets", vbInformation, "Sheets removed"
End Sub
但是當我輸入特定文本（前后沒有空格）時，它不起作用。任何想法如何解決它？這是我對其進行測驗的作業表中的資料：作業表名稱
這是宏的結果：宏的結果
uj5u.com熱心網友回復：
like 的問題是它在使用*Name*.
您可以使用 InStr 來查找您的字串：
If InStr(1, xWs.Name, shName ) > 0 Then
xWs.Delete
cnt = cnt 1
End If
uj5u.com熱心網友回復：
您必須使用InStr而不是 ' like' 并從最后一張紙回圈到第一張紙
For i = ThisWorkbook.Sheets.Count To 1 Step -1
Set xWs = ThisWorkbook.Sheets(i)
If InStr(xWs.Name, shName ) > 0 Then
xWs.Delete
cnt = cnt 1
End If
Next i
uj5u.com熱心網友回復：
Like區分大小寫。嘗試對您的代碼進行以下更改（請參閱以 開頭的注釋'****）
Private Sub CommandButton28_Click()
Dim shName As String
Dim xName As String
Dim xWs As Worksheet
Dim cnt As Integer
shName = Application.InputBox("Enter the specific text:", "Delete sheets", _
ThisWorkbook.ActiveSheet.Name, , , , , 2)
If shName = "" Then Exit Sub
'**** use LCase() here
xName = "*" & LCase(shName) & "*"
' MsgBox xName
Application.DisplayAlerts = False
cnt = 0
For Each xWs In ThisWorkbook.Sheets
'**** Use LCase() here
If LCase(xWs.Name) Like xName Then
xWs.Delete
'MsgBox xName
cnt = cnt 1
End If
Next xWs
Application.DisplayAlerts = True
MsgBox "Have deleted " & cnt & " worksheets", vbInformation, "Sheets removed"
End Sub
另請注意，您的代碼不會檢查要洗掉的作業表是否是作業簿中的唯一作業表（將引發錯誤）。此外，如果用戶發送*（有意或錯誤地）您的代碼將洗掉除一張之外的所有作業表。這很危險，因此請考慮您的代碼策略并采取相應措施。一個想法是在洗掉作業表之前保存備份副本
Sub BackupWorkbook(wb As Workbook)
wb.SaveCopyAs "FULL_BACKUP_PATH" & Format(Now, "yyyymmddhhmmss") & ThisWorkbook.Name
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/519606.html
標籤：擅长vba
上一篇：獲取點擊物件/圖片的參考
下一篇：提取word檔案中的文本參考