☰

通过输入框插入新列时出现运行时错误

tag-icon • tag-icon microsoft-excel vba
通过输入框插入新列时出现运行时错误

当我运行此 vba 时出现运行时错误 1004 应用程序定义或对象定义错误

Sub InsertColumns()
    Dim ColumnNumber As String
    Dim NumColumns As Long

    ' Get column letter from user
    ColumnNumber = InputBox("Enter the column letter where you want to insert a new column:")

    ' Remove space character from column letter
    ColumnNumber = Trim(ColumnNumber)

    ' Convert column letter to column number
    ColumnNumber = Range(ColumnNumber & "1").Column

    ' Get number of columns to insert from user
    NumColumns = InputBox("Enter the number of columns to insert:")

    ' Insert columns
    Columns(ColumnNumber).Resize(NumColumns).EntireColumn.Insert shift:=xlToRight, copyorigin:=xlFormatFromLeftOrAbove
End Sub

答案1

Columns(ColumnNumber)您的错误来自最后一条语句的第一部分 ( )。您已将其定义ColumnNumber为字符串。但Columns期望的是索引，即数字（整数、长整型等）。

您应该：

  • 转换ColumnNumber为整数：Int(ColumnNumber)
  • 或者定义一个整数变量并赋值ColumnNumber给它

如果你进行转换，你的代码将如下所示：

Sub InsertColumns()
    Dim ColumnNumber As String
    Dim NumColumns As Long

    ' Get column letter from user
    ColumnNumber = InputBox("Enter the column letter where you want to insert a new column:")

    ' Remove space character from column letter
    ColumnNumber = Trim(ColumnNumber)

    ' Convert column letter to column number
    ColumnNumber = Range(ColumnNumber & "1").Column

    ' Get number of columns to insert from user
    NumColumns = InputBox("Enter the number of columns to insert:")

    ' Insert columns
    Columns(Int(ColumnNumber)).Resize(NumColumns).EntireColumn.Insert shift:=xlToRight, copyorigin:=xlFormatFromLeftOrAbove
End Sub

相关内容