我想從 Excel VBA 的回圈中排除一些作業表。
我使用了以下提示： https ://superuser.com/questions/1299900/exclude-sheets-when-looping-through-sheets
我的代碼如下所示：
Dim ws As Worksheet
Set ws = Application.Worksheets.Count
For i = 1 To a
If ws.Name <> "BoQ" And ws.Name <> "Sign Off Sheet" And ws.Name <> "PIANOI" Then
Worksheets(i).Cells(46, 14).Formula = "=Frontsheet!J10"
Worksheets(i).Cells(46, 16).Formula = "=Frontsheet!J9"
End If
Next
但我收到一個錯誤：
型別不匹配
試圖通過使用此提示來解決它：
VBA 編譯錯誤型別不匹配 - 使用作業表函式回圈
但徒勞無功。
我怎樣才能在這里排除一些作業表？
uj5u.com熱心網友回復：
那是真正的代碼大雜燴。:)
在ws回圈中用作回圈變數For Each：
Dim ws As Worksheet
For Each ws in Activeworkbook.worksheets
If ws.Name <> "BoQ" And ws.Name <> "Sign Off Sheet" And ws.Name <> "PIANOI" Then
ws.Cells(46, 14).Formula = "=Frontsheet!J10"
ws.Cells(46, 16).Formula = "=Frontsheet!J9"
End If
Next
uj5u.com熱心網友回復：
撰寫代碼的藝術之一是重新定位樣板代碼，以便您可以專注于要實作的邏輯。@Rory 向您展示了如何洗掉圍繞迭代集合的樣板
下面的代碼顯示了使用 ArrayList 來避免 If 陳述句中的多個子句。
Option Explicit
' Put the following in the module where you keep global variables
Const ExcludeNames As String = "BoQ,Sign Off Sheet,PIANOI"
' use this function to create the ArrayList of names you wish to exclude
' ArrayList requires a reference to mscorlib
Public Function SetupExcludedWorksheets(ByVal ipStringOfNames As String) As ArrayList
Dim myName As Variant
Dim myExcludes As ArrayList
Set myExcludes = New ArrayList
For Each myName In ipStringOfNames.Split(",")
myExcludes.Add myName
Next
Set SetupExcludedWorksheets = myExcludes(excludeNames)
End Function
' in your setup routines include the following lines
Dim myExcludes As ArrayList
Set myExcludes = SetupExcludedWorksheets(ExcludeNames)
' Your code now becomes
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If Not myExcludes.contains(ws.Name) Then
ws.Cells(46, 14).Formula = "=Frontsheet!J10"
ws.Cells(46, 16).Formula = "=Frontsheet!J9"
End If
Next
當然，您可以修改上面的代碼，得到一個只包含您希望處理的名稱的串列。
uj5u.com熱心網友回復：
復制到帶有排除項的多個作業表
我的愿景
Sub CopyFormulae()
Const WORKSHEET_EXCLUSIONS_LIST As String _
= "BoQ,Sign Off Sheet,PIANOI" ' maybe you want to add 'Frontsheet'?
Dim WorksheetExclusions() As String
WorksheetExclusions = Split(WORKSHEET_EXCLUSIONS_LIST, ",")
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet
For Each ws In wb.Worksheets
With ws
If IsError(Application.Match(.Name, WorksheetExclusions, 0)) Then
.Range("N46").Formula = "=Frontsheet!J10"
.Range("P46").Formula = "=Frontsheet!J9"
'Else ' is found in the WorksheetExclusions array; do nothing
End If
End With
Next ws
MsgBox "Formulae copied.", vbInformation
End Sub
你的愿景（按我的喜好順序）
- 我更喜歡我的想法，因為當我檢查作業表名稱時
Application.Match，這是不區分大小寫的，即A = a，如果我在區分大小寫方面拼錯了名稱，即如果我決定將最后一個作業表重命名為PianoI，我不必像使用以下代碼那樣在代碼中更改它。 - 研究 If 陳述句（它在所有三個解決方案中都是相同的）但更努力地研究其余部分。
Sub ForEachNext()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim ws As Worksheet
For Each ws In wb.Worksheets
With ws
If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
And .Name <> "PIANOI" Then
.Range("N46").Formula = "=Frontsheet!J10"
.Range("P46").Formula = "=Frontsheet!J9"
End If
End With
Next ws
End Sub
Sub ForNext()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim wsCount As Long: wsCount = wb.Worksheets.Count
Dim ws As Worksheet
Dim n As Long
For n = 1 To wsCount
Set ws = wb.Worksheets(n)
With ws
If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
And .Name <> "PIANOI" Then
.Range("N46").Formula = "=Frontsheet!J10"
.Range("P46").Formula = "=Frontsheet!J9"
End If
End With
Next n
End Sub
Sub ForNextFewerVariables()
Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
Dim n As Long
For n = 1 To wb.Worksheets.Count
With wb.Worksheets(n)
If .Name <> "BoQ" And .Name <> "Sign Off Sheet" _
And .Name <> "PIANOI" Then
.Range("N46").Formula = "=Frontsheet!J10"
.Range("P46").Formula = "=Frontsheet!J9"
End If
End With
Next n
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/535465.html
標籤：擅长VBA
上一篇：在Excel中比較兩個字串