通过字符串数组循环，错误'9'

问题描述：

我无法弄清我们为什么会收到错误'9'。我声明了字符串数组，然后我想使用数组值来设置表名。我做错了什么？通过字符串数组循环，错误'9'

Dim wsText() As String 
Dim sht As Worksheet 
Dim wSum As Worksheet 
Dim service As String 
Dim supplier As String 
Dim priceRange As String 
Dim price As String 
Dim Lrow As Integer, LastRow As Integer 

Set sht = ThisWorkbook.Worksheets(4) 
Set wSum = ThisWorkbook.Worksheets(Summary) 

wsText = Array("<25K", "25K <100K", "100K <250K", "250K <500K", "500K <1M", "1M <5M", "5M <15M", "15M <30M", "30M <50M") 

LastRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row 

    For Each element In wsText 
     For i = 5 To LastRow 
      For j = 13 To 47 

       Lrow = wSum.UsedRange.Rows(wSum.UsedRange.Rows.Count).Row + 1 

       service = ThisWorkbook.Worksheets(element.Value).Cells(i, 1).Text 
       supplier = ThisWorkbook.Worksheets(element.Value).Cells(4, j).Text 
       priceRange = ThisWorkbook.Worksheets(element.Value).Cells(2, 1).Text 
       price = ThisWorkbook.Worksheets(element.Value).Cells(i, j).Text 

       wSum.Cells(Lrow, 1) = service 
       wSum.Cells(Lrow, 2) = supplier 
       wSum.Cells(Lrow, 3) = priceRange 
       wSum.Cells(Lrow, 4) = price 
      Next j 
     Next i 
    Next element 


End Sub

答

对于初学者这部分是错误的ThisWorkbook.Worksheets(Summary)这里总结作为变量，但不能看到你的代码分配给它。这将抛出错误9.如果工作表名称本身是摘要，那么在双引号内使用它，如ThisWorkbook.Worksheets("Summary")。

++打我吧:)删除了我发表 –

感谢一吨....一些成就.... :) – cyboashu

非常感谢你！我恨我的自我，我不能仔细查看代码！ –

答

声明您的阵列为替代品

例如，

Dim wsText as variant 
wsText = Array("<25K", "25K <100K", "100K <250K", "250K <500K", "500K <1M", "1M <5M", "5M <15M", "15M <30M", "30M <50M")

参考： Declare and Initialize String Array in VBA

通过字符串数组循环，错误'9'

相关推荐