excel VBA：下标超出范围错误：For循环：多个工作表-表创建

nuypyhwy 于 2023-04-13 发布在其他

关注(0)|答案(1)|浏览(153)

VBA新手：代码在第一个工作表上运行良好，但在其余工作表上抛出错误

Dim st As Worksheet     
Set st = ActiveSheet 
  
  For Each ws In ThisWorkbook.Worksheets
     ws.Activate

  ''--------------------------------''
 'Print lables on worksheet'
 ''------------------------------''
 ws.Cells(2, 15).value = "Greatest_increase"
 ws.Cells(3, 15).value = "Greatest_decrease"
 ws.Cells(4, 15).value = "Greatest total"
 ws.Cells(1, 16).value = "name"
 ws.Cells(1, 17).value = "Value"

  'Print values on worksheet'
 ''------------------------------------------------------''
  ws.Range("P2").value = name1
  ws.Range("P3").value = name2
  ws.Range("P4").value = name3
  ws.Range("Q2").value = GreatIncrease
  ws.Range("Q3").value = GreatDecrease
  ws.Range("Q4").value = GreatTotal
 
 `'Create a table "Growth_Table" for range("O1:Q4")'
  '-----------------------------------------------------------------------''
  Dim tablename As String
  Dim TableExists As Boolean
  
  'tablename = "Growth_Table"      
   TableExists = False
  
On Error GoTo Skip
If ActiveSheet.ListObjects("Growth_Table").Name = "Growth_Table" Then
TableExists = True
End If
Skip:
    On Error GoTo 0
     
If Not TableExists And (ws.Range("O2").value = "Greatest_increase") Then
     
    ActiveSheet.ListObjects.Add(xlSrcRange, ws.Range("O1:Q4"), , xlYes).Name = "Growth_Table"
    ActiveSheet.ListObjects("Growth_Table").TableStyle = "TableStyleLight9"
     
Else
    Exit Sub
     
         End If
 

      Next 
st.Activate

工作表被命名为A、B、C、D。我想通过循环来运行所有工作表的代码。但是代码在sheetA上运行良好，但在sheetB上抛出'下标超出范围。是因为，“Growth Table”已经存在于工作表A上吗？有任何修复方法吗？
请帮帮忙！

excel

来源：https://stackoverflow.com/questions/65678200/vbasubscript-out-of-range-error-for-loop-multiple-worksheets-table-creation

1条答案

按热度按时间

mzsu5hc01#

为每个工作表添加表

测试时未将值写入工作表。
密码

Option Explicit

Sub addTables()
    
    Const tblName As String = "Growth_Table"
    Const tblAddress As String = "O1:Q4"
    Const tblStyle As String = "TableStyleLight9"
    
    Dim ash As Worksheet: Set ash = ActiveSheet
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    
    For Each ws In ThisWorkbook.Worksheets
     
        'Write lables to worksheet
        ws.Range("O2").Value = "Greatest_increase"
        ws.Range("O3").Value = "Greatest_decrease"
        ws.Range("O4").Value = "Greatest total"
        ws.Range("P1").Value = "Name"
        ws.Range("Q1").Value = "Value"
        
        'Write values to worksheet
        ws.Range("P2").Value = name1
        ws.Range("P3").Value = name2
        ws.Range("P4").Value = name3
        ws.Range("Q2").Value = GreatIncrease
        ws.Range("Q3").Value = GreatDecrease
        ws.Range("Q4").Value = GreatTotal
         
        'Try to create a reference to (set) the table
        Set tbl = Nothing
        On Error Resume Next
        Set tbl = ws.ListObjects(tblName)
        On Error GoTo 0
     
        'Create table.
        If tbl Is Nothing Then 'Table does not exist
            Set tbl = ws.ListObjects.Add(xlSrcRange, _
                ws.Range(tblAddress), , xlYes)
            tbl.Name = tblName
            tbl.TableStyle = tblStyle
        'Else 'Table already exists
        End If
    
    Next ws
    
    ash.Activate

End Sub

赞(0）回复(0）举报 2023-04-13

我来回答

excel VBA：下标超出范围错误：For循环：多个工作表-表创建

1条答案

为每个工作表添加表

相关问题

热门标签

最新问答