Excel VBA-复制到特定工作表的代码中的错误，具体取决于范围

debugcn 发表于 Dev

布莱恩·班尼特（Brian Bennett）

JNevill非常友好地清理并改进了我放在一起的宏，不幸的是它返回了错误，并且我无法得到对原始帖子的回复。希望有人可以在他离开的地方接机。

我得到的错误是-对象变量或未设置块变量，这与行相反 lastCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

Sub Macro1()
'Make a variable to store the cell found
Dim lastCell as Range

'find the last cell in Column A of the active sheet
lastCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

'Paste in the I16 value
lastCell.value = RangE("I16").value

'Grab whatever is hanging out in Column B next to the last cell and stick it in J20
Range("J20").value = lastCell.Offset(0,1).value 

'Test to see if I16 has value "R"
If Range("I16").value = "R" Then

    'Find the last row in Sheet7, Column B and store it to the variable
    lastCell = Range("B" & Rows.Count).End(xlUp).Offset(1)

    'Copy J20 value to the lastCell in Sheet 7, Column B
    lastCell = Range("J20").value
End if


End Sub

原帖是在这里ORIGINAL

罗里

您只是想念Set（因为它是一个对象变量）：

Set lastCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

根据下面的评论，我认为这是您想要的东西：

Sub Macro1()
    Dim ws                    As Worksheet
    'Make a variable to store the cell found
    Dim lastCell              As Range

    'find the last cell in Column A of the active sheet
    Set lastCell = Range("A" & Rows.Count).End(xlUp).Offset(1)

    'Paste in the I16 value
    lastCell.Value = Range("I16").Value

    'Grab whatever is hanging out in Column B next to the last cell and stick it in J20
    Range("J20").Value = lastCell.Offset(0, 1).Value

    'Test to see if I16 has value "R"
    Select Case VBA.UCase$(Range("I16").Value2)
        Case "R"
            Set ws = sheet7
        Case "C"
            Set ws = sheet3
        Case "P"
            Set ws = Sheet1
        Case "S"
            Set ws = Sheet2
    End Select

    If Not ws Is Nothing Then
        'Find the last row in Sheet7, Column B and store it to the variable
        Set lastCell = ws.Range("B" & Rows.Count).End(xlUp).Offset(1)

        'Copy J20 value to the lastCell in Sheet 7, Column B
        lastCell = Range("J20").Value
        Application.Goto lastCell, True
    End If

End Sub

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。