英文:
Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet
问题
Sub Macro1()
'
Dim wsList() As String, wsName As Variant, ws As Worksheet
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
For Each wsName In wsList
Set ws = ThisWorkbook.Sheets(wsName)
Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("O31:AF34").Select
ActiveCell.FormulaR1C1 = "FALL"
Range("O35").Select
Next wsName
End Sub
英文:
I have 10 sheets labeled 1-10 respectively and I want to loop through each sheet and format the same ranges in each sheet.
The below code works but I cannot get it to loop through each sheet
Sub Macro1()
'
Dim wsList() As String, wsName As Variant, ws As Worksheet
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
For Each wsName In wsList
Set ws = ThisWorkbook.Sheets(wsName)
Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27").Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("O31:AF34").Select
ActiveCell.FormulaR1C1 = "FALL"
Range("O35").Select
Next wsName
End Sub
</details>
# 答案1
**得分**: 1
Here's the translated content:
如果你写 `Range(...)`,VBA 将始终使用 `ActiveSheet`。你想要的是它在工作表 `ws` 上运行,因此你必须告诉 VBA:`ws.Range(...)`。
另外,没有必要使用 `Select` 和 `Selection`(你应该避免使用它,几乎从不需要):相反,要么将 Range 分配给一个变量并使用它,要么使用 `With` 子句:
Dim wsList() As String, wsName As Variant, ws As Worksheet
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
For Each wsName In wsList
Set ws = ThisWorkbook.Sheets(wsName)
' 选项 a) 使用变量
Dim myRange as Range
Set myRange = ws.Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27")
With myRange.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' 或者,不使用 With
myRange.Interior.Pattern = xlNone
myRange.Interior.TintAndShade = 0
myRange.Interior.PatternTintAndShade = 0
' 选项 b) 直接使用中间变量的 With
With ws.Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29")
.Locked = False
.FormulaHidden = False
End With
ws.Range("O31:AF34").FormulaR1C1 = "FALL"
Next wsName
顺便说一下,你的关于范围 "O31:AF34" 的公式看起来奇怪。这不是一个有效的公式。如果你想将文本 ""FALL"" 写入单元格,请使用 `ws.Range("O31:AF34").Value = "FALL"`。
<details>
<summary>英文:</summary>
If you write `Range(...)`, VBA will always use the `ActiveSheet`. What you want is that is works on worksheet `ws`, so you have to tell VBA it: `ws.Range(...)`.
Also, there is no need to use `Select` and `Selection` (and you should avoid it, it is almost never needed): Instead, either assign the Range to a variable and use that, or use a `With`-clause:
Dim wsList() As String, wsName As Variant, ws As Worksheet
wsList = Split("1 2 3 4 5 6 7 8 9 10", " ")
For Each wsName In wsList
Set ws = ThisWorkbook.Sheets(wsName)
' Option a) Use a Variable
Dim myRange as Range
Set myRange = ws.Range("A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27")
With myRange.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' Or, without With
myRange.Interior.Pattern = xlNone
myRange.Interior.TintAndShade = 0
myRange.Interior.PatternTintAndShade = 0
' Option b) Use With directly with intermediate variable
With ws.Range("B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29")
.Locked = False
.FormulaHidden = False
End With
ws.Range("O31:AF34").FormulaR1C1 = "FALL"
Next wsName
Your formula for the range "O31:AF34" looks odd, btw. This is not a valid formula. If you want to write the text `"FALL"` into the cell, use `ws.Range("O31:AF34").Value = "FALL"`
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论