vba - 尝试运行此程序，但出现错误 "compile error, wrong number of arguments, or invalid property assignment"

Question

Private Sub CommandButton1_Click()

LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

    For i = 2 To LastRow

        If Cells(i, 1) = "Wheat" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


            ActiveSheet.Cells(erow, 51).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If

    Next i

        For i = 2 To LastRow

            If Cells(i, 1) = "Feeder Cattle" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


            ActiveSheet.Cells(erow, 3).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If


    Next i

        For i = 2 To LastRow

            If Cells(i, 1) = "Corn" Then
            Range(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Select
            Selection.Copy


            Workbooks.Open Filename:="C:\commodities\allcommodities-new.xlsm"
            Worksheets("Sheet2").Select

            erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

            ActiveSheet.Cells(erow, 67).Select
            ActiveSheet.Paste
            ActiveWorkbook.Save
            ActiveWorkbook.Close
        End If

    Next i
end sub

注意:代码在第一个“Range”命令处失败，并出现“编译错误，参数数量错误，或属性分配无效”我可以使用 Range 命令中的 2 个单元格定义来运行代码。

Answer 1

虽然您可以声明range("B1, C1, D1")，但您不能声明range("B1", "C1", "D1") 这就是你正在尝试做的事情。

如果您确实想要第 2、3 和 4 列位于第 i 行，则只需使用第一个和最后一个，例如 range("B1:D1")

Range(Cells(i, 2), Cells(i, 4)).Select

如果实际列是不连续的组，则使用 Union。

dim rng as range
set rng = union(Cells(i, 2), Cells(i, 4), Cells(i, 6))
rng.select

请查看How to avoid using Select in Excel VBA macros .

Option Explicit

Private Sub CommandButton1_Click()
    Dim i As Long, lastRow As Long, nextRow As Long
    Dim wbACN As Workbook

    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Set wbACN = Workbooks.Open(Filename:="C:\commodities\allcommodities-new.xlsm")

    For i = 2 To lastRow
        Select Case LCase(Cells(i, 1).Value2)
            Case "wheat"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "AY").End(xlUp).Offset(1, 0)
            Case "feeder cattle"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
            Case "corn"
                Union(Cells(i, 2), Cells(i, 3), Cells(i, 4)).Copy _
                  Destination:=wbACN.Worksheets("Sheet2").Cells(Rows.Count, "BO").End(xlUp).Offset(1, 0)
            Case Else
                'do notbhing
        End Select
    Next i

    wbACN.Close savechanges:=True

End Sub