Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

huangapple go评论68阅读模式
英文:

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&quot).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" 的公式看起来奇怪。这不是一个有效的公式。如果你想将文本 "&quot;FALL&quot;" 写入单元格,请使用 `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)
      
    &#39; Option a) Use a Variable
    Dim myRange as Range
    Set myRange = ws.Range(&quot;A4:C7,L4:Q5,L6:L7,S24:U27,AD24:AF27,AO28:AR29,AO26:AO27&quot;)
    
    With myRange.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    &#39; Or, without With
    myRange.Interior.Pattern = xlNone
    myRange.Interior.TintAndShade = 0
    myRange.Interior.PatternTintAndShade = 0

    &#39; Option b) Use With directly with intermediate variable
    With ws.Range(&quot;B4:C7,M4:Q5,T24:U27,AE24:AF27,AP28:AR29&quot;)
        .Locked = False
        .FormulaHidden = False
    End With

    ws.Range(&quot;O31:AF34&quot;).FormulaR1C1 = &quot;FALL&quot;
Next wsName
Your formula for the range &quot;O31:AF34&quot; looks odd, btw. This is not a valid formula. If you want to write the text `&quot;FALL&quot;` into the cell, use `ws.Range(&quot;O31:AF34&quot;).Value = &quot;FALL&quot;`

</details>



huangapple
  • 本文由 发表于 2023年3月21日 02:39:09
  • 转载请务必保留本文链接:https://go.coder-hub.com/75794108-2.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定