excel - 如何记录一个excel表的数据并在间隔后存储另一个表

Question

我得到了一个 VBA 代码，但我无法逐行捕获数据。它仅存储按列的数据。

Set Capture = Worksheets("Sheet1").Range("A1:C5")

此代码首先捕获数据 A1 到 A5 列，然后在同一行中捕获 B1 到 B5 列，依此类推。但我试图在一行中捕获 First A1 到 C1，然后在下一行中捕获 A2 到 C2，依此类推。有什么办法可以点吗？ 问题截图

Dim NextTime As Double



Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Interval = 5    'Number of seconds between each recording of data
Set Capture = Worksheets("Sheet1").Range("A1:C5") '****Problem in this code.Only****
With Worksheets("Sheet2")   'Record the data on this worksheet
Set cel = .Range("A5")  'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
For i = 1 To 5
cel.Offset(0, 1 + (i - 1) * Capture.Rows.Count).Resize(1, Capture.Rows.Count).Value = 
Application.Transpose(Capture.Columns(i).Value)
Next i

End With

NextTime = Now + Interval / 86400
Application.OnTime NextTime, "RecordData"
End Sub

标签： excelvba

Answer 1

正确的代码可以改写为：

Dim NextTime As Double 
Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Interval = 5    'Number of seconds between each recording of data
Set Capture = Worksheets("Sheet1").Range("A2:N2") 'Capture this column of data
With Worksheets("Sheet2")   'Record the data on this worksheet
Set cel = .Range("A5")  'First timestamp goes here
Set cel = .Cells(.Rows.Count, cel.Column).End(xlUp).Offset(1, 0)
cel.Value = Now
cel.Offset(0, 1).Resize(1, Capture.Cells.Count).Value = Capture.Value


End With

NextTime = Now + Interval / 86400
Application.OnTime NextTime, "RecordData"
End Sub