结合片材回路 [英] Incorporating sheet loop

问题描述

你好，我有这个代码，只运行在一张单（sheet3），但我希望它循环工作簿的其他工作表，并运行这个代码。我尝试使用每个循环，但它似乎不符合这个代码。 Ive查找了其他循环方法，但我真的不确定我该怎么做。

这是代码

  Sub DeleteCells（）
 Dim rng As Range，rngError As Range，delRange As Range 
 Dim i As Long，j As Long 
 
 On Error Resume Next 
 Set rng = Application.InputBox（Select要删除的单元格，类型：= 8）
错误GoTo 0 
 
如果rng不是，然后退出子Else rng.Delete 
 
使用表格（ Sheet3）
对于i = 1到7'<〜〜循环槽A到G 
'~~>检查该列是否有任何错误
 On Error Resume Next 
设置rngError = .Columns（i）.SpecialCells（xlCellTypeFormulas，xlErrors）
错误GoTo 0 
 
如果没有rngError是Nothing然后
对于j = 1到100'< ~~循环通过行1到100 
如果.Cells（j，i）.Text =#REF！然后
'~~>存储要删除的范围
如果delRange不是，然后
设置delRange = .Columns（i）
退出
 Else 
设置delRange = Union（delRange，。列（i））
结束如果
结束如果
下一个
结束如果
下一个
结束
 
'~~ >删除一个范围
如果不是delRange是没有，然后delRange.Delete 
 End Sub 
  

解决方案

通常您可以使用索引＃循环使用表格，或者为每个表单提到。所以添加到您的代码中将意味着：

  Sub DeleteCells（）
 
 Dim rng As Range，rngError As Range，delRange As Range 
 Dim i As Long，j As Long，k as long 
 Dim wks as Worksheet 
 
 On Error Resume Next 
 
设置rng = Application.InputBox（选择要删除的单元格，类型：= 8）
 
错误GoTo 0 
 
如果rng不是，则退出子Else rng.Delete 
 
 for k = 1给本工作簿。 workheets.count通过所有工作表
 
 set wks = thisworkbook.worksheets（k）
 
与wks 
 
对于i = 1至7' <〜〜循环槽A到G 
 
'~~>检查该列是否有任何错误
 On Error Resume Next 
 
设置rngError = .Columns（i）.SpecialCells（xlCellTypeFormulas，xlErrors）
 
错误GoTo 0 
 
如果不是rngError不是然后
对于j = 1到100'< ~~循环通过行1到100 
如果.Cells（j，i）.Text = ＃REF！然后
'~~>存储要删除的范围
如果delRange不是，然后
设置delRange = .Columns（i）
退出
 Else 
设置delRange = Union（delRange，。列（i））
结束如果
结束如果
下一个j 
结束如果
 
下一个i 
 
结束与
 
下一个k 
 
'~~>删除一个范围
如果不是delRange是没有，然后delRange.Delete 
 
 End Sub 
  

通常还有一个更好的名称为下一个，因为你有一个更好的概述，...下一个循环关闭。

Hi there i have this code which only runs on a single sheet(sheet3) but i want it to loop through other sheets of the workbook and run this code. I tried using the for each loop but it does not seem to be compatible with this code. Ive looked up other methods of looping but im really unsure of how do i go about it .

Here is the code

Sub DeleteCells()
    Dim rng As Range, rngError As Range, delRange As Range
    Dim i As Long, j As Long

    On Error Resume Next
    Set rng = Application.InputBox("Select cells To be deleted", Type:=8)
    On Error GoTo 0

    If rng Is Nothing Then Exit Sub Else rng.Delete

    With Sheets("Sheet3")
        For i = 1 To 7 '<~~ Loop trough columns A to G
            '~~> Check if that column has any errors
            On Error Resume Next
            Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors)
            On Error GoTo 0

            If Not rngError Is Nothing Then
                For j = 1 To 100 '<~~ Loop Through rows 1 to 100
                    If .Cells(j, i).Text = "#REF!" Then
                        '~~> Store The range to be deleted
                        If delRange Is Nothing Then
                            Set delRange = .Columns(i)
                            Exit For
                        Else
                            Set delRange = Union(delRange, .Columns(i))
                        End If
                    End If
                Next
            End If
        Next
    End With

    '~~> Delete the range in one go
    If Not delRange Is Nothing Then delRange.Delete
End Sub

解决方案

Usually you can loop through sheets using their index #, or the mentioned for each... So added to your code this would mean:

Sub DeleteCells()

Dim rng As Range, rngError As Range, delRange As Range
Dim i As Long, j As Long, k as long
Dim wks as Worksheet

On Error Resume Next

Set rng = Application.InputBox("Select cells To be deleted", Type:=8)

On Error GoTo 0

If rng Is Nothing Then Exit Sub Else rng.Delete

for k = 1 to thisworkbook.worksheets.count 'runs through all worksheets

  set wks=thisworkbook.worksheets(k)

  With wks

    For i = 1 To 7 '<~~ Loop trough columns A to G

        '~~> Check if that column has any errors
        On Error Resume Next

        Set rngError = .Columns(i).SpecialCells(xlCellTypeFormulas, xlErrors)

        On Error GoTo 0

        If Not rngError Is Nothing Then
            For j = 1 To 100 '<~~ Loop Through rows 1 to 100
                If .Cells(j, i).Text = "#REF!" Then
                    '~~> Store The range to be deleted
                    If delRange Is Nothing Then
                        Set delRange = .Columns(i)
                        Exit For
                    Else
                        Set delRange = Union(delRange, .Columns(i))
                    End If
                End If
             Next j
         End If

     Next i

  End With

next k

'~~> Delete the range in one go
If Not delRange Is Nothing Then delRange.Delete

End Sub

Usually it is also better to name the "next", because you have a better overview which for...next loop is closed.

这篇关于结合片材回路的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！