代码之家 › 专栏 › 技术社区 › mjac

动态RTD数据记录到另一张表

rtd row range vba excel

mjac · 技术社区 · 7 年前

当数据显示在C4:C7列的范围内时,此代码工作正常,但是我的工作表是使用C4:K4中的一行数据设置的。我无法计算出一行而不是一列。有什么帮助吗?谢谢 view pic with issue

Sub RecordData()
Dim Interval As Double
Dim cel As Range, Capture As Range
Interval = 30 'Number of seconds between each recording of data
Set Capture = Worksheets("Sheet1").Range("C4:K4") 'Capture this row of data
With Worksheets("Sheet2") 'Record the data on this worksheet
    Set cel = .Range("A2") '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 = Application.Transpose(Capture.Value)
End With
NextTime = Now + Interval / 86400
Application.OnTime NextTime, "RecordData"
End Sub

1 回复 | 直到 7 年前

Excelosaurus 7 年前

换位 Capture.Value

建议:避免按Excel中工作表选项卡上显示的名称引用工作表。这些名称通常会随着时间的推移而改变,并会导致代码中断。例如 With Worksheets("Sheet2")... 一旦将Sheet2的名称更改为其他名称,将失败并出现错误9“下标超出范围”。

CodeName . 您可以从Visual Basic编辑器中进行设置,方法是单击项目资源管理器中的工作表,然后在“属性”窗口中指定(名称)属性,如下所示,在该窗口中,我已将代号“SourceWs”指定给名为“源工作表”的工作表,如Excel中所示:

然后,您的代码可以重写为:

Option Explicit

Sub RecordData()
    Dim Interval As Double
    Dim cel As Range, Capture As Range
    Dim NextTime As Date

    Interval = 30 'Number of seconds between each recording of data

    Set Capture = SourceWs.Range("C4:K4") 'Capture this row of data

    With DestWs 'Record the data on this worksheet
        Set cel = .Range("A2") '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

excel mvp blog 你会发现比你能处理的更多。玩得高兴