英文:
Fill Formula down to all rows
问题
使用VBA代码，我尝试在D2列中填充一个公式一直填充到表格的最后一行，但出于某种原因，它只填充了53行（然后必须循环到所有其他工作表并执行相同操作）。我做错了什么？
Sub Step_3()
Dim ws As Worksheet
Dim N As Long
For Each ws In ActiveWorkbook.Worksheets
N = Cells(Rows.Count, "E").End(xlUp).Row
ws.Range("D2:D" & N).Formula = "=C2-E2"
Next ws
End Sub
这是你提供的VBA代码的翻译部分，没有其他内容。
英文:
Using VBA code, I'm trying to fill a formula in column D2 all the way down the column, to the last row of the table, but, for some reason, it only fills down 53 rows (and then it must loop to all the other sheets and do the same). What am I doing wrong?
Sub Step_3()
Dim ws As Worksheet
Dim N As Long
For Each ws In ActiveWorkbook.Worksheets
N = Cells(Rows.Count, "E").End(xlUp).Row
ws.Range("D2:D" & N).Formula = "=C2-E2"
Next ws
End Sub
答案1
得分: 2
问题在于在没有任何工作表父引用的情况下使用 `Cells(Rows.Count, "E").End(xlUp).Row`，它将始终引用活动工作表并始终返回相同的行。
要解决这个问题，我们需要在范围对象前加上 `ws` 前缀：
N = ws.Cells(Rows.Count, "E").End(xlUp).Row
所以：
Sub Step_3()
Dim ws As Worksheet
Dim N As Long
For Each ws In ActiveWorkbook.Worksheets
N = ws.Cells(Rows.Count, "E").End(xlUp).Row
ws.Range("D2:D" & N).Formula = "=C2-E2"
Next ws
End Sub
英文:
The issue is that using Cells(Rows.Count, "E").End(xlUp).Row without any worksheet parent reference it will always refer to the active sheet and always return the same row.
To fix that we need to prefix the range object with ws:
N = ws.Cells(Rows.Count, "E").End(xlUp).Row
So:
Sub Step_3()
Dim ws As Worksheet
Dim N As Long
For Each ws In ActiveWorkbook.Worksheets
N = ws.Cells(Rows.Count, "E").End(xlUp).Row
ws.Range("D2:D" & N).Formula = "=C2-E2"
Next ws
End Sub
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论