对于工作表中的每个工作表不工作 [英] For each Worksheet in Worksheets not working

问题描述

我很喜欢VBA编码，所以请原谅我的愚昧，因为我可能在做一些超简单的错误。我写了一个小的VBA脚本，用于将工作表的数据对齐，使用名称的主列表作为参考点。

它在一个工作表上一次工作正常，但是当输入对于工作表中的每个工作表循环时，它将其应用到第一个工作表，然后停止。我已经调试了它，for each部分正在运行，但代码没有被应用到第一个工作表之上。任何提示？

我已经尝试在for each变量的内部和外部移动Dim，似乎没有任何区别。所有对代码中工作表的引用都是根据我的理解而改变每个步骤的变量。

我按照建议对代码进行了编辑，它似乎只适用于第一个工作表：

  Option Explicit 
 
 Sub AlignAll（）
 Dim current As Worksheet 
 Dim n As Long，x As Long，i As Long，as As Range，c As Range 
 Set c = Worksheets（Masterlist）。范围（A6：A200）
 
对于i = 1 To ThisWorkbook.Worksheets.Count 
设置当前= ThisWorkbook.Sheets（i）
如果Current.Name& ; YearlyAnd Current.Name<> Masterlist然后'防止冲突
n = Cells.SpecialCells（11）.Row 
'设置为当前工作表的范围，c为主列表的范围
设置a =当前.Range（A6：A200）
a（n + 1）= Chr（255）：c（n + 1）= Chr（255）
 a.Sort a（1），1， ：= xlNo 
 c.Sort c（1），1，标题：= xlNo 
 Do 
x = x + 1'遍历每个范围
如果一个（x）> c（x）然后'根据需要添加行以对齐数据
 a（x）.EntireRow.Insert xlShiftDown 
 End If 
如果x> 10 ^ 4然后退出Do 
循环直到a（x）= Chr（255）和c（x）= Chr（255）
a（x）.ClearContents：c（x）.ClearContents' 
 End If 
 Next i 
 End Sub 
  

解决方案

其他简单的解决方法：

  Option Explicit 
 
 Sub AlignAll（）
 Dim Current As Worksheet 
 Dim Current As Worksheet 
 Dim n As Long，x As Long，i as Long'
 Dim a As Range 
 
 on error下一个简历真的需要吗？可能会解决问题（暂时）删除它，调试... 
 
对于i = 1到ActiveWorkbook.Worksheets.count 
 set Current = ActiveWorkbook.Sheets（i）
 'Do Stuff 
 Next i 
 End Sub 
  

I'm rather new to VBA coding, so excuse my ignorance, as I'm probably doing something super simple here wrong. I've written a small VBA script that aligns data for a worksheet, using a master list of names as a reference point.

It works fine on one worksheet at a time, but when entered into a "For each worksheet in worksheets" loop, it applies it to the first worksheet and then stops. I've debugged it, and the "for each" part is working, but the code isn't being applied past the first worksheet. Any tips?

I've tried moving the Dim both inside and outside of the "for each" variable, and it seems to make no difference. All references to the worksheet in the code are the variable that changes with each step, from my understanding.

I've made edits to the code as suggested, and it still seems to only apply to the first worksheet:

Option Explicit

Sub AlignAll()
Dim Current As Worksheet
Dim n As Long, x As Long, i As Long, a As Range, c As Range
Set c = Worksheets("Masterlist").Range("A6:A200")

For i = 1 To ThisWorkbook.Worksheets.Count
    Set Current = ThisWorkbook.Sheets(i)
        If Current.Name <> "Yearly" And Current.Name <> "Masterlist" Then 'Prevent Conflicts
            n = Cells.SpecialCells(11).Row
            'sets a as the range of the current worksheet and c as range of the master list
            Set a = Current.Range("A6:A200")
            a(n + 1) = Chr(255): c(n + 1) = Chr(255)
            a.Sort a(1), 1, Header:=xlNo
            c.Sort c(1), 1, Header:=xlNo
            Do
            x = x + 1 'steps through each range
            If a(x) > c(x) Then 'adds rows as needed to align data
                a(x).EntireRow.Insert xlShiftDown
            End If
            If x > 10 ^ 4 Then Exit Do
            Loop Until a(x) = Chr(255) And c(x) = Chr(255)
            a(x).ClearContents: c(x).ClearContents 'resets variables
        End If
Next i
End Sub

解决方案

Other simple workaround:

Option Explicit

Sub AlignAll()
Dim Current As Worksheet
Dim Current As Worksheet
Dim n As Long, x As Long, i as Long '
Dim a As Range

on error resume next 'really needed ? might solve problems to (temporarely) remove it , to debug...

For i=1 to ActiveWorkbook.Worksheets.count
   set Current = ActiveWorkbook.Sheets(i)
   'Do Stuff
Next i
End Sub

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