VBA-运行时错误9:复制和粘贴 [英] VBA - Run Time Error 9: Copy and paste

问题描述

我最近了解了vba.

我已经创建了一个包含一些字段的表单，该字段功能是

I've make a form with some fields, this fields function are

1. 工作表名称
2. 范围复制
3. 范围粘贴

Private Sub CommandButton1_Click()
    Dim barisawal As String
    Dim sampaiawal As String
    Dim tujuanawal As String
    Dim tujuanakhir As String


    barisawal = bariske.Text
    barisakhir = TextBox2.Text
    tujuanawal = TextBox3.Text
    tujuanakhir = TextBox4.Text
    sheetawals = sheetawal.Text
    sheetakhirs = sheettujuan.Text

    Worksheets("sheetawals").Range("barisawal:barisakhir").Copy
    Worksheets("Sheetakhirs").Range("tujuanawal:tujuanakhir").Paste

End Sub

单击提交按钮CommandButton1时，出现运行时错误#9下标超出范围".

When I click my submit button CommandButton1, I get a runtime error #9 "Subscript out of range".

这是我想做的:用户将用粘贴的工作表名称，范围和工作表目标填充表单.

Here what I want to do: the user will fill the form with worksheet name, range and worksheet destination for paste.

推荐答案

此行上出现下标超出范围错误:

Worksheets("sheetawals").Range("barisawal:barisakhir").Copy

VBA将"sheetawals"理解为文字字符串，因此它试图在活动工作簿中查找名为sheetawals的工作表.如果找不到它，它会因运行时错误9/下标超出范围而爆炸.

The "sheetawals" is understood by VBA as a literal string, so it's trying to find a sheet named sheetawals in the active workbook. When it doesn't find it, it blows up with runtime error 9 / subscript out of range.

@tospig正确提示意味着使用您定义的变量，而不是字符串文字-通过删除双引号，如果指定的工作表名称存在于活动工作簿中，则可以解决问题 .与Range相同-您需要使用字符串串联运算符&来串联字符串，如下所示:

As @tospig correctly hinted, you mean to use the variables you've defined, not string literals - by removing the double quotes you'll fix your problem if the specified sheet names exist in the active workbook. Same for Range - you need to concatenate the strings using the string concatenation operator &, like this:

Worksheets(sheetawals).Range(barisawal & ":" & barisakhir).Copy

与下一行代码相同.

现在，您需要添加一些错误处理，以防止在用户提供的值不合法时发生运行时错误.这是通过On Error语句完成的.使该代码成为您过程中的第一行可执行代码:

Now, you'll need to add some error-handling to prevent a runtime error when the user-provided values aren't legal. That's done with an On Error statement. Make that the first executable line of code in your procedure:

On Error GoTo ErrHandler

然后在End Sub之前添加以下内容:

And then just before End Sub, add something like this:

    Exit Sub

ErrHandler:
    MsgBox "Invalid selection! Please try again."

这篇关于VBA-运行时错误9:复制和粘贴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！