vba复制粘贴动态范围

Question

不习惯使用VBA。我有一个程序，记录一个数据系列的1分钟快照，并创建一个日志。然后我想要对log X列进行快照来填充其他快照列。1分钟前，10分钟前，60分钟前的数据，等等。代码似乎工作得很好，只要填写足够的日志表。但如果它只是8分钟前，它有一个错误，试图复制10分钟前的数据，不存在。我似乎不明白为什么这艘潜艇不像我想象的那样工作。

运行时错误'1004'应用程序或对象定义错误。我想我的If语句可以消除这个问题。

下面是我的代码:

Sub tenMinBack()
Dim rng As Range
Application.CutCopyMode = False
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Log").Select
rng = Range(Cells(1, Columns.Count).End(xlToLeft).Offset(0,-10), Cells(13,Columns.Count).End(xlToLeft).Offset(0,-10)).Select
#### So I know this rng is pointing to cells that don't exist for first 10 minutes,
#### Not sure why this If statement isn't stopping it from attempting to copy it
If Not IsEmpty(rng) Then
rng.Copy
ThisWorkBook.Worksheets("Prices").Range("D3:D15").PasteSpecial xlValues
ThisWorkBook.Worksheets("Log").Cells.EntireColumn.AutoFit
ThisWorkBook.Worksheets("Prices").Cells.EntireColumn.AutoFit
End If
ThisWorkBook.Worksheets("Prices").Select
End Sub

Answer 1

试试这个

Sub tenMinBack()

Application.CutCopyMode = False
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Log").Select
col = Cells(1, Columns.Count).End(xlToLeft).Column - 10
If col > 0 Then
Range(Cells(1, col), Cells(13, col)).Copy
ThisWorkbook.Worksheets("Prices").Range("D3:D15").PasteSpecial xlValues
ThisWorkbook.Worksheets("Log").Cells.EntireColumn.AutoFit
ThisWorkbook.Worksheets("Prices").Cells.EntireColumn.AutoFit
End If
ThisWorkbook.Worksheets("Prices").Select
End Sub