我试图创建一个子程序,将工作表中的各种值存储在变量中,然后将变量复制到列表对象(表)中的适当列的空行中。我一直遇到以下错误:
“运行时错误'91': 对象变量或With块变量未设置”
我尝试了很多变化,但仍然会出现错误,有时甚至会出现不同的错误。非常感谢您的任何建议。这是代码
感谢您的帮助。
“运行时错误'91': 对象变量或With块变量未设置”
我尝试了很多变化,但仍然会出现错误,有时甚至会出现不同的错误。非常感谢您的任何建议。这是代码
Sub RecordData()
Dim strName1 As String, strName2 As String
Dim dTimeStamp As Date
Dim sItem1 As Single, sItem2 As Single, sItem3 As Single
Dim sItem4 As Single
Dim ws_1 As Worksheet, ws_2 As Worksheet
Set ws_1 = Sheets("Data")
Set ws_2 = Sheets("Output")
strName1 = ws_1.Range("D1").MergeArea.Cells(1, 1).Value
strName1 = Left(strName1, Len(strName1) - 6)
strName2 = ws_1.Range("B17")
dTimestamp = Now
sItem1 = ws_1.Range("D3")
sItem2 = ws_1.Range("E3")
sItem3 = ws_1.Range("F3")
sItem4 = ws_1.Range("N3")
Dim loTable As ListObject
Set loTable = ws_2.ListObjects("CurrentMkts")
Dim iLastRow As Integer
iLastRow = loTable.Range.Rows.Count
If iLastRow = 2 Then
Dim iTempRow As Integer
iTempRow = iLastRow - 1
loTable.DataBodyRange.Cells(iTempRow, 1).Value = dTimeStamp
loTable.DataBodyRange.Cells(iTempRow, 2).Value = sItem1
loTable.DataBodyRange.Cells(iTempRow, 3).Value = sItem2
loTable.DataBodyRange.Cells(iTempRow, 4).Value = sItem3
loTable.DataBodyRange.Cells(iTempRow, 6).Value = sItem4
loTable.ListRows.Add
Else
iTempRow = iLastRow
loTable.DataBodyRange.Cells(iTempRow, 1).Value = dTimeStamp
loTable.DataBodyRange.Cells(iTempRow, 2).Value = sItem1
loTable.DataBodyRange.Cells(iTempRow, 3).Value = sItem2
loTable.DataBodyRange.Cells(iTempRow, 4).Value = sItem3
loTable.DataBodyRange.Cells(iTempRow, 6).Value = sItem4
loTable.ListRows.Add
End If
End Sub
感谢您的帮助。
loTable.ListRows.Add
移动到设置单元格值之前的那一行。由于某种原因,如果ListObject为空,Excel认为它没有DataBodyRange。如果有实际数据行,则“IF”块的第二部分不需要将loTable.ListRows.Add
移到顶部。 - Scott Holtzman