暂且不论是否Collection
对于此任务来说是一个好主意,让我们检查您的代码:
General
您可能已经这样做过,但不需要重复
Use Option Explicit
并声明所有变量
你的代码
Sheets.Add
这增加了一个新的Worksheet
到活跃的Workbook
,并将其放在当前活动工作表之前,然后激活新工作表。
问题:您依赖默认行为来获取新的Worksheet
,您无法确定新工作表位于何处,并且您没有对它的引用。
建议:
获取对工作簿对象的引用并在整个代码中使用它
Dim wb As Workbook
Set wb = ActiveWorkbook
控制添加到工作簿的内容、位置和方式
Dim wsSummary as Worksheet
Set wsSummary = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsSummary.Name = "Summary"
你的代码
For Each wsheet In Worksheets
myavg.Add wsheet.Range("B2:B11")
Next
这创建了一个Collection
of Range
的,从每个Worksheet
在书中,包括我们刚刚创建的那本书。我猜你不想包括那一个。为了便于维护,您应该创建一个变量来保存范围并在整个代码中使用它。
让我们应用上面的内容
Dim wsheet as WorkSheet
Dim RangeAddress As String
RangeAddress = "B2:B11"
For Each wsheet In wb.Worksheets
If wsheet.name <> wsSummary.Name Then
myavg.Add wsheet.Range(RangeAddress)
End If
Next
你的代码
For i = 1 To myavg.Count
avg1 = Application.WorksheetFunction.Average(Range("B2:B11"))
Next
问题:您正在迭代Collection
但不在循环中引用它。每次通过你计算avg1
然后在下一个循环中覆盖它。
每次通过循环,您都会计算活动工作表上范围的平均值(这将是刚刚添加的空白新工作表)
建议:
向前跳转,您似乎想要在新工作表上列出平均值,连续行中每张工作表一个。因此,让我们将平均值存储在一个数组中,以便稍后放在汇总表上。请注意,如果有任何Range
是空的,Average
会导致错误。
Dim avg() as Variant
Redim avg(1 to myavg.Count, 1 To 1)
For i = 1 To myavg.Count
avg(i, 1) = Application.WorksheetFunction.Average(myavg(i))
Next
你的代码
curColumn = 5
curRow = 4
For i = 1 To myavg.Count
ActiveSheet.Cells(curRow, curColumn).Value = avg1
curRow = curRow + 1
Next
问题:
这段代码只是简单地把最后计算出的值avg1
到连续的行上
将结果放入新工作表中:将平均值收集到数组中后,将其放入工作表中
Dim curColumn as Long, curRow As Long
curColumn = 5
curRow = 4
wsSummary.Cells(curRow, curColumn).Resize(UBound(avg, 1), 1) = avg
目前还没有处理的是Error Handling
。代码可能出错的方式有很多种,因此明智的做法是包括Error Handling
在你的代码中。例如,如果已经有一个名为“Summary”的工作表怎么办?如果什么Average
返回错误?
谈到这是否是一个好方法,应该清楚的是,在创建了一个数组来保存结果后,可以在工作簿的传递中填充该数组。
像这样的东西
Sub Demo()
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim RangeAddress As String
RangeAddress = "B2:B11"
Dim wsSummary As Worksheet
Dim avg() As Variant
ReDim avg(1 To wb.Worksheets.Count, 1 To 1)
For i = 1 To UBound(avg, 1)
With wb.Worksheets(i).Range(RangeAddress)
If WorksheetFunction.Count(.Value) > 0 Then
avg(i, 1) = WorksheetFunction.Average(.Value)
Else
avg(i, 1) = "No Values On Sheet"
End If
End With
Next
Dim curColumn As Long, curRow As Long
curColumn = 5
curRow = 4
On Error Resume Next
Set wsSummary = wb.Worksheets("Summary")
If Err.Number <> 0 Then
' Summary sheet does not exist, create it
Err.Clear
On Error GoTo 0
Set wsSummary = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
wsSummary.Name = "Summary"
Else
On Error GoTo 0
' Summary sheet already exists
' what do you wnat to do?
End If
wsSummary.Cells(curRow, curColumn).Resize(UBound(avg, 1), 1) = avg
End Sub
该代码可能仍然存在问题,具体取决于您的具体要求(例如,如果“摘要”表已经存在,这将在平均循环中处理它)