vba 偏移和结束功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7654997/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Offset and end function
提问by icobes
I am trying to paste data from Q3 Sheet 1 to Q3 Sheet 2. Each piece of data should be pasted one row below the last piece of data on Q3 Sheet 2 (starting in cell A4). Unfortunately, the line
我正在尝试将数据从 Q3 Sheet 1 粘贴到 Q3 Sheet 2。每条数据都应粘贴在 Q3 Sheet 2 上最后一条数据下方的一行（从单元格 A4 开始）。不幸的是，该行
Worksheets("Q3 Sheet 2").Range("A3").End(xlUp).Offset(1, 0) = .Offset(iRow, 0)
does not do this. Instead it pastes all the data in A4 and they continue to overwrite each other, so that there is only one entry in A4 when there should be multiple entries from A4 all the way up to A14. Please help. Thanks!
不这样做。相反，它将所有数据粘贴到 A4 中，并且它们继续相互覆盖，因此当从 A4 一直到 A14 应该有多个条目时，A4 中只有一个条目。请帮忙。谢谢！
With Worksheets("Q3 Sheet 1").Range("A3")
'Count total number of entries
nCustomers = Range(.Offset(1, 0), .Offset(1, 0).End(xlDown)).Rows.Count
'Loop through all entries looking for amounts owed > 1000
For iRow = 1 To nCustomers
AmountOwed = .Offset(iRow, 1) - .Offset(iRow, 2)
'If amount owed > 1000 then transfer customer ID and amount owing to Q3 Sheet 2
If AmountOwed > 1000 Then
Worksheets("Q3 Sheet 2").Range("A3").End(xlUp).Offset(1, 0) = .Offset(iRow, 0)
End If
Next iRow
End With
采纳答案by Robert Mearns
Only two small changes are needed.
只需要两个小的改变。
Worksheets("Q3 Sheet 2").Range("A3").End(xlUp).Offset(1, 0) = .Offset(iRow, 0)
should read
应该读
Worksheets("Q3 Sheet 2").Range("A2").End(xlDown).Offset(1, 0) = .Offset(iRow, 0)
回答by brettdj
I've rewritten the code to work with ranges (rather than use a range to get rows then loop row numbers), dimension the variables and with screenupdating off (for speed), plus it is more robust to look up than down when finding the last record
我已经重写了代码以处理范围（而不是使用范围来获取行然后循环行号），对变量进行标注并关闭屏幕更新（为了速度），而且在查找时向上查找比向下查找更健壮最后记录
This version copies the entire rowfrom Q3 Sheet 1 to Q3 Sheet 2 if amount owned exceeds 1000. It can be cut back to whatever amount of cells your want (I think you may want two cells?)
如果拥有的数量超过 1000，此版本会将整行从 Q3 Sheet 1复制到 Q3 Sheet 2。它可以减少到你想要的任何数量的单元格（我想你可能想要两个单元格？）
[pdate: Tidied code further, added a ws2variable, removed AmountOwnedand redundant nCustomers]
[pdate: 进一步整理代码，添加了一个ws2变量，删除AmountOwned和冗余nCustomers]
Sub Update()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim rng1 As Range
Dim rng2 As Range
Application.ScreenUpdating = False
Set ws = Worksheets("Q3 Sheet 1")
Set ws2 = Worksheets("Q3 Sheet 2")
Set rng1 = ws.Range(ws.[a4], ws.Cells(Rows.Count, "A").End(xlUp))
For Each rng2 In rng1
'If amount owed > 1000 then transfer customer ID and amount owing to Q3 Sheet 2
If rng2.Offset(0, 1) - rng2.Offset(0, 2) > 1000 Then rng2.EntireRow.Copy ws2.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Next
Application.ScreenUpdating = True
End Sub
回答by Tim Williams
Worksheets("Q3 Sheet 2").cells(rows.count,1).End(xlUp).Offset(1, 0) = .Offset(iRow, 0)
Assuming there's no data lower down the sheet in column A
假设 A 列中的工作表下方没有数据
回答by Bruno Leite
Change this line to
将此行更改为
Worksheets("Q3 Sheet 2").Range("A3").End(xlDown).Offset(1, 0) = .Offset(iRow, 0)
[]'s
[] 的
