excel - 在我的工作簿中的某些工作表上执行相同的两个计算

Question

工作表包含不同行长的表格

我正在尝试在表格上执行两次计算以得出平均值。然后在我的工作簿中的每张纸上进行相同的计算。有“x”张纸。每张表中的表格在长度上是动态的。我正在尝试解决此代码中的错误

Sub CalcOnSheets()

Application.ScreenUpdating = False
Dim row As Integer
Dim lastRow As Long
Dim ActiveWorksheet As Long
Dim ThisWorksheet As Long
Dim n As Integer

Dim x As Integer
x = Sheets.Count

 For n = 3 To x


Sheets(n).range("I2:Q" & lastRow + 3).Columns.AutoFit
Sheets(n).range("D2:D" & lastRow + 3).ColumnWidth = 15


lastRow = Sheets(n).Cells(Rows.Count, "D").End(xlUp).row
If lastRow > 1 Then

 For row = 2 To lastRow


Sheets(n).range("B1:P" & lastRow + 3).NumberFormat = "£#,##0.00_); 
(£#,##0.00)"
Sheets(n).range("I1:I" & lastRow + 3).NumberFormat = "#,##0_);(#,##0)"
Sheets(n).range("Q1:Q" & lastRow + 3).NumberFormat = "#0.0%_);(#0.0%)"

If Sheets(n).Cells(row, 4).Value <> "" Then
Sheets(n).Cells(row, 16).Value = Sheets(n).Cells(row, 10).Value - 
Sheets(n).Cells(row, 11).Value - _
Sheets(n).Cells(row, 12).Value - Sheets(n).Cells(row, 13).Value - 
Sheets(n).Cells(row, 14).Value - Sheets(n).Cells(row, 15).Value
Sheets(n).Cells(row, 17).Value = Sheets(n).Cells(row, 16).Value / 
Sheets(n).Cells(row, 10).Value

End If
Next

Dim r As range, j As Long, k As Long, z As Long
j = Sheets(n).range("A1").End(xlToRight).Column

'This adds up the totals on the sheet
'changing the first value of k stops it adding up un-needed columns
For k = 9 To j
Set r = Sheets(n).range(Sheets(n).Cells(1, k), Sheets(n).Cells(1, 
k).End(xlDown))
r.End(xlDown).Offset(2, 0) = WorksheetFunction.Sum(r)
r.End(xlDown).Offset(3, 0) = WorksheetFunction.Average(r)
Next k

'This deletes the total of the profit margins which is meaningless
Sheets(n).range("Q" & lastRow).Offset(2) = ""


'This changes the format of column I average value back to two decimal places
Sheets(n).range("I" & lastRow).Offset(3).NumberFormat = "0.00_);(0.00)"

'Calculation 1   This calculates Average profit per unit
Sheets(n).range("B" & lastRow).Offset(2, 0).Value = Sheets(n).range("P" & 
lastRow).Offset(2).Value / Sheets(n).range("I" & lastRow).Offset(2).Value


'Calculation 2    This calculates Average profit per order
Sheets(n).range("A" & lastRow).Offset(2, 0).Value = Sheets(n).range("P" & 
lastRow).Offset(2).Value / Rows("D").Column.Count



Else

MsgBox ("There is no data at column D")
End If

Application.ScreenUpdating = True


Next n

End Sub

标签： excelvbaif-statementexcel-formula