对于Workbook中的每个工作表 [英] For Each Worksheet in Workbook

问题描述

关于为什么下面的代码不会循环通过工作表的任何想法？

我正在根据工作表名称在每个工作表中设置一个列。它被卡在活动工作表上，忽略如果ws.Name<> 默认。这是一个模块：

  Sub LangID_update（）
 
 Dim wb As Workbook：Set wb = ThisWorkbook 
 Dim ws As Worksheet 
 Dim LastCol As Integer 
 Dim LastRow As Integer 
 Dim rng As Range 
 
 Application.ScreenUpdating = False 
 
对于每个ws在wb.Worksheets 
如果ws.Name<> Default然后
 
 LastCol = ws.Cells（1，Columns.count）.End（xlToLeft）.Column 
 LastRow = ws.Cells（Rows.count，1）.End xlUp）.Row 
设置rng =范围（单元格（2，LastCol + 1），单元格（LastRow，LastCol + 1））
带有rng 
对于每个c In Range（Cells（2 ，LastCol + 1），Cells（LastRow，LastCol + 1））
如果ws.Name =ARGS则c.Value =ESP
如果ws.Name =AUTS则c。 Value =GR
如果ws.Name =DEUS则c.Value =GR
 
下一步c 
结束
结束If 
 Next 
 
 Application.ScreenUpdating = True 
设置wb =没有
设置ws =没有
设置rng =没有
 
 End Sub 
  

解决方案

您正在使用的许多对象反映都是不合格的，因此，请参考活动工作表。在每个对象的开头添加 ws。对象限定条件。

Any thoughts on why the below code would not be looping through the worksheets?

I'm trying to set a column in each sheet based on what the sheet name is. It's getting stuck on the active worksheet, and ignoring the If ws.Name <> "Default". This is built as a module:

Sub LangID_update()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet
Dim LastCol As Integer
Dim LastRow As Integer
Dim rng As Range

Application.ScreenUpdating = False

For Each ws In wb.Worksheets
If ws.Name <> "Default" Then

LastCol = ws.Cells(1, Columns.count).End(xlToLeft).Column
LastRow = ws.Cells(Rows.count, 1).End(xlUp).Row
Set rng = Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
    With rng
        For Each c In Range(Cells(2, LastCol + 1), Cells(LastRow, LastCol + 1))
            If ws.Name = "ARGS" Then c.Value = "ESP"
            If ws.Name = "AUTS" Then c.Value = "GR"
            If ws.Name = "DEUS" Then c.Value = "GR"

        Next c
    End With
    End If
Next

Application.ScreenUpdating = True
Set wb = Nothing
Set ws = Nothing
Set rng = Nothing

End Sub

解决方案

Many of the object refences that you are using are unqualified and therefore refer to the active sheet. Add the ws. object qualification at the beginning of each object.

这篇关于对于Workbook中的每个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！